Please help how to format date

[suvechha_b]

Facing problem when loading date field from excel file.

Please find the excel file and sample model attached.

The problem is on date format of Start Date  as because my source excel file having start date : 2015/02/23

I have formatted to "dd/mm/yyyy" in excel. But it is showing in excel but pulling wrong data in qlikview.

[suvechha_b]

I cant change the

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';

format

[avinashelite]

try like this :

=if(IsNum([Start Date])='-1',Date(([Start Date]),'DD/MM/YYYY'),Date(num#([Start Date]),'DD/MM/YYYY'))

this will work

[suvechha_b]

Can any one help me how to write in expression level.

As I dont want to change the script of qlikview.

Is going to affect other calculation.

I cant change the

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';

format

The dimension expression :

LEFT(Date#([Start Date],'YYYY-MM-DD'),2) &'/'& RIGHT(LEFT(Date#([Start Date],'YYYY-MM-DD'),5),2) & '/' & RIGHT(Date#([Start Date],'YYYY-MM-DD'),4)

[avinashelite · Accepted solution]

try this in the front end or calculated dimension it will work:

=if(IsNum([Start Date])='-1',Date(([Start Date]),'DD/MM/YYYY'),Date(num#([Start Date]),'DD/MM/YYYY'))

please find the attachment for the same