Date Format: M/DD/YYYY and DD/M/YYYY to DD/M/YYYY

[carol_wu]

Dear Experts,

Now I have dates with date format both in 'M/DD/YYYY' and 'DD/M/YYYY' in qlikview. How can I convert all date with format in 'M/DD/YYYY' to 'DD/M/YYYY'?

Many Thanks! Date example attached.

[Chanty4u]

try

date(date#(Datefield,'M/DD/YYYY'),'DD/M/YYYY') as NewDate

[MK_QSL]

SET DateFormat='DD/M/YYYY';

Data:

LOAD

IF(IsNum(Date),Date(Date), Date(Date#(Date,'M/DD/YYYY'))) as Date

FROM

date.xlsx

(ooxml, embedded labels, table is Sheet1);