Date issues

[_armoco_]

Hi,

So I have this excel with dates. Half of the dates are in MM-DD-YYYY format and the other half in MM/DD/YYYY.

Now here is the thing. I load this thing into QlikView and I get this:

Note that the second half of the data is now suddenly in DD-MM-YYYY instead of the original format MM/DD/YYYY.

Been playing around with date functions for sometime now and nothing seems to work.

How do I fix this and bring all the dates into a single format? I don't mind if its in DD-MM-YYYY or MM-DD-YYYY or anything, but I just need it in the same format.

Attaching the file.

Thanks,

Arjun

[devarasu07]

Hi,

Try like this format in your script

Date(Floor(Date),'D/M/YYYY') as DateNew;

[_armoco_]

Hi Devarasu,

Thank you for your reply. Tried it out:

Dates upto 12th are missing now

Thanks,

Arjun

[tresesco]

Are you sure the dates in red highlighted are in MM-DD-YYYY format? The DD-MM part in screen shows no value bigger than 12, so it could be DD-MM-YYYY also.

[Anil_Babu_Samineni]

May be play around using alt - script and chart function ‒ QlikView

[_armoco_]

Hi Tresesco,

I'm sure its MM-DD-YYYY. Its my clients data from 1st to 21st December

Thanks,
Arjun

[_armoco_]

Hi,

Tried Date(Floor(Date),'D/M/YYYY') as DateNew;

Dates are still messed up

Thanks and Regards,

Arjun

[jonathandienst]

You have two date formats in your input. This will get all the dates:

     ...

     Date(Alt(Date#(Date, 'yyyyMMdd'), Date#(Date, 'MMddyyyy'), 'ddMMyyyy') as Date,

     ...

Adjust the last format string to the format you require if you don't want DMY.

[tresesco · Accepted solution]

Your source data is not so. If you load the as text you would see those dates have numbers which are actually not of Dec months. Hence the correction would be required at the source. Because, if you have the date value (the number) wrong itself at the backend, no formatting, nor parsing can change the date.

[_armoco_]

Thank you! I guess that will solve it.