Excel Number to Date Format in Qlikview

[MalcolmCICWF]

So I hate dates... they never seem to work how they should or have in the past....

I have an Excel spreadsheet I have to load into Qlikview. I am pulling in a field that is a date but of course it is being treated as a number 20180601. I am trying to import it in typical ways and convert this to an actual date to use. Typically I use...

DATE(DATE#(FIELD,'YYYYMMDD'),'YYYYMMDD')  and get nothing

I tried  DATE#(FIELD,'YYYYMMDD')   and get 21336

I tried  DATE(FIELD,'YYYYMMDD')   and get 19580531

What am I doing wrong this time...?  Once I get it as a date I can use interval to get the days between this date and Today()

[ehilsinger]

Can you post a sample file?  Your first example worked for me.

[Gysbert_Wassenaar]

I tried  DATE#(FIELD,'YYYYMMDD')   and get 21336

I tried  DATE(FIELD,'YYYYMMDD')   and get 19580531

Then the actual numeric value is has in excel is 21336. No idea what excel did to format it as 20180601, but it's real numeric value must be 21336 given your results above.

[MalcolmCICWF]

I swear to you, It is formatted as General and displaying as 20180601 in Excel, if I just load that into Qlikview that is also how it shows up

[MalcolmCICWF]

I posted a small sample and removed all sensitive data besides the one field that contains the date

[ehilsinger]

Your first statement works with the CSV file you posted.  When you say you get nothing, what do you mean?

[ehilsinger · Accepted solution]

It is showing up in QlikView as 20180601 because you specified the display format in the Date function as YYYYMMDD.  If you change it to MM/DD/YYYY you will see it change format.

[Gysbert_Wassenaar]

That's a csv file, not an excel file. A csv file only contains text and no formatting. If I load your csv in qlikview everything works as expected.