Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
MalcolmCICWF
Contributor II

Excel Number to Date Format in Qlikview

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

1 Solution

Accepted Solutions
ehilsinger
New Contributor III

Re: Excel Number to Date Format in Qlikview

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.

7 Replies
ehilsinger
New Contributor III

Re: Excel Number to Date Format in Qlikview

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

MVP & Luminary
MVP & Luminary

Re: Excel Number to Date Format in Qlikview

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.


talk is cheap, supply exceeds demand
MalcolmCICWF
Contributor II

Re: Excel Number to Date Format in Qlikview

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
Contributor II

Re: Excel Number to Date Format in Qlikview

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

ehilsinger
New Contributor III

Re: Excel Number to Date Format in Qlikview

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

ehilsinger
New Contributor III

Re: Excel Number to Date Format in Qlikview

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.

MVP & Luminary
MVP & Luminary

Re: Excel Number to Date Format in Qlikview

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.


talk is cheap, supply exceeds demand