Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Kohli
Contributor 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
Valued Contributor III

Re: Mixed date format in a single field

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

Channa

Re: Mixed date format in a single field

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

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

Re: Mixed date format in a single field

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
Honored Contributor II

Re: Mixed date format in a single field

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
Valued Contributor III

Re: Mixed date format in a single field

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
Honored Contributor

Re: Mixed date format in a single field

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