Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
[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.
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
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