Convert date filed?

[qlikuser222]

Hi All,

I have data in excel in below format

Period

1-Jan-2017

1-Feb-2017

1-Mar-2017

when I load in Qlik using below function but  it throws null, I am not sure what is the problem. When I use the the same formula on front end it works .

Load

date(date#(Period,'M/D/YYYY'),'DD/MM/YYYY') as Period  // gives nothing but blanks

I want to show Period as 01/01/2017, 01/02/2017, 01/03/2017.

Can you please help me?

Thanks

[Peter_Cammaert]

How do you import your date values from Excel? By reading an .xls or xlsx file? Then it's pretty simple. Together with the string representations, Excel stores the same underlying DateTime binary values as QlikView does, and QV will recognize them immediately. No need to interprete the text values, because they're just ... text.

Just format them into the display format you like.

[qlikuser222]

Thanks for your reply,

but how can I convert

1-Jan-2017

1-Feb-2017

1-Mar-2017

From excel to

01/01/2017

01/02/2017

01/03/2017

in Qlik?

[Digvijay_Singh]

If you can share sample Excel, it would help. The expression you shared is not interpreting source format as per the values you have shared, Did u try this? -

date(date#(Period,'D-MMM-YYYY'),'DD/MM/YYYY') as Period

[Peter_Cammaert · Accepted solution]

Either set your default format to something like 'MM/DD/YYYY' (top of your load script) or use a Date() function to format the field string represntation when loading columns from Excel. Like in

LOAD ...

     Date(MyExcelDateField, 'MM/DD/YYYY') AS MyExcelDateField,

     ...

FROM MyExcelFile.xls (options);

If this doesn't work as-is, please post a data sample in an Excel file in this thread.