Date Format DD/MM/YYYY

[Purushothaman]

HI Experts,

Attached is the DOB column in different formats, I would like to transform to single format DD/MM/YYYY.

I tried using ALT, DATE# and DATE functions, I am unable to bring all the DOB rows into DD/MM/YYYY.

Anyone, Please help!!

Thank you,

Regards,

Puru

[vikasmahajan]

[Sheet1]:
LOAD
Date([DOB],'DD/MM/YYYY' ) AS [DOB]
FROM [lib://DataFiles/QLIKcOMM.xlsx]
(ooxml, embedded labels, table is Sheet1);

this should give you result.

[Aditya_Chitale · Accepted solution]

@Purushothaman ,

for formatting using script use:

if(istext(DOB),Date(Date#(DOB,'DD/MM/YYYY'),'DD/MM/YYYY'),Date(num#(DOB),'DD/MM/YYYY'))

for formatting in frontend chart use:

if(istext(DOB),Date(Date#(DOB,'DD/MM/YYYY'),'DD/MM/YYYY'),Date(Date#(num#(DOB)),'DD/MM/YYYY'))

Regards,

Aditya

[BrunPierre · Accepted solution]

Hi, since you have a mixed date format, try this.

Date(
Alt(
Date#(DOB,'DD/MM/YYYY'),
Date#(DOB,'DD-MMM-YY'),
Date(Num#(DOB))),'DD/MM/YYYY') as DOB