topic Re: Can't Convert Excel date in QlikView

Can't Convert Excel date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

in excel i have a column, DateRec

in the daterec column it has dates like:

Date Rec

21/02/2018 08:43

21/02/2018 01:16

I can't seem to read these into Qlik. It says they are custom cells on excel dd/mm/yyyy hh:mm

On qlik i simply want the dd/mm/yyyy hh:mm to be DD/MM/YYYY on the load script. when i convert to general on the cell, they convert to numbers like 43151.34977

anyone help?

Re: Can't Convert Excel date

YoussefBelloum — Mon, 16 Jul 2018 15:25:58 GMT

Hi,

try this on the script when loading that field:

Date(Timestamp#([Date Rec],'dd/mm/yyyy hh:mm'),'dd/mm/yyyy') as [Date Rec]

Re: Can't Convert Excel date

sunny_talwar — Mon, 16 Jul 2018 15:26:16 GMT

Have you tried loading it like this

Date(Floor([Date Rec]), 'DD/MM/YYYY') as [Date Rec]

or this

Date(Floor(Date#([Date Rec], 'DD/MM/YYYY hh:mm')), 'DD/MM/YYYY') as [Date Rec]

Re: Can't Convert Excel date

Anonymous — Mon, 16 Jul 2018 15:41:54 GMT

Thanks - this worked

Re: Can't Convert Excel date

sunny_talwar — Mon, 16 Jul 2018 16:06:37 GMT

Make sure to add Floor() because without floor, you will see a date, but underlying will still be a timestamp

Re: Can't Convert Excel date

YoussefBelloum — Mon, 16 Jul 2018 16:12:09 GMT

as mentioned above, you should use this to get unique date values

Date(Floor(Timestamp#([Date Rec],'dd/mm/yyyy hh:mm')),'dd/mm/yyyy') as [Date Rec]

Re: Can't Convert Excel date

sunny_talwar — Mon, 16 Jul 2018 16:21:17 GMT

Also, Make sure to use MM for month... because mm is for minutes

Date(Floor(Timestamp#([Date Rec],'dd/mm/yyyy hh:mm')),'dd/mm/yyyy') as [Date Rec]

I know you and the OP already know this, but not everyone knows about this... so that is why putting it out here

Re: Can't Convert Excel date

YoussefBelloum — Mon, 16 Jul 2018 20:51:03 GMT

‌I Really completely forget about this... thought using it 2 times (as months and minutes) in the same #function is enough to be recognized as months and minutes. Will make some tests but thanks for pointing this out stalwar1‌