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
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist III
Specialist III

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

Channa

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

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
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.
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.
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

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