topic Re: Excel Number to Date Format in Qlikview in QlikView

Excel Number to Date Format in Qlikview

MalcolmCICWF — Mon, 04 Jun 2018 17:29:15 GMT

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()

Re: Excel Number to Date Format in Qlikview

ehilsinger — Mon, 04 Jun 2018 17:42:37 GMT

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

Re: Excel Number to Date Format in Qlikview

Gysbert_Wassenaar — Mon, 04 Jun 2018 17:43:03 GMT

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.

Re: Excel Number to Date Format in Qlikview

MalcolmCICWF — Mon, 04 Jun 2018 18:40:17 GMT

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

Re: Excel Number to Date Format in Qlikview

MalcolmCICWF — Mon, 04 Jun 2018 18:48:22 GMT

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

Re: Excel Number to Date Format in Qlikview

ehilsinger — Mon, 04 Jun 2018 19:41:12 GMT

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

Re: Excel Number to Date Format in Qlikview

ehilsinger — Mon, 04 Jun 2018 19:42:51 GMT

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.

Re: Excel Number to Date Format in Qlikview

Gysbert_Wassenaar — Tue, 05 Jun 2018 08:15:32 GMT

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.