Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
cancel
Showing results for 
Search instead for 
Did you mean: 
Kohli
Creator II
Creator II

Mixed date format in a single field

I have date field in my xl file. Like 

Date:

DD.MM.YY
DD.MM.YYYY
DD-MM-YY
DD-MM--YYYY

I want  convert into single format like DD-MM-YY. please suggest me.

6 Replies
Channa
Specialist III
Specialist III

=date(date#(datefield,'DD/MM/YY'),'DD/MM/YY')

Channa
PrashantSangle

use alt().
see in help menu for details.

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
pradosh_thakur
Master II
Master II

assuming your date field's name is date_field and theya re real dates but not string try the below

DATE(date_field,'DD-MM-YY')
Learning never stops.
pradosh_thakur
Master II
Master II

if they are strings try the below

alt(date(date#(purgechar(date_field,'.-')'DDMMYYYY'),'DD-MM-YY'),
date(date#(purgechar(date_field,'.-')'DDMMYY'),'DD-MM-YY')
)

 

 

Learning never stops.
mdmukramali
Specialist III
Specialist III

Hi,

As suggested by @PrashantSangle you can try to use Alt function.

Syntax:

Date(Alt(Date#(Date, 'FirstFormat'), Date#(Date, 'SecondFormat'), 'RequiredFormat') as Date

 

Example:

Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

Date#(MixDate,'M/D/YYYY'),

Date#(MixDate,'D/M/YYYY'),

Date#(MixDate,'YYYYMMDD'),

Date#(MixDate,'DD.MM.YYYY'),

Date#(MixDate,'YYYY-MM-DD')

) as MixDate

 

Thanks,

Mohammed Mukram

Frank_Hartmann
Master II
Master II

try this:

Alt( 
 Date(Date#(Date,'DD-MM--YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD-MM-YY'),'DD-MM-YY') 
 ) as Date