Can't Convert Excel date

[Anonymous]

  Hi

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?

[YoussefBelloum · Accepted solution]

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]

[sunny_talwar]

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]

[Anonymous]

Thanks - this worked

[sunny_talwar]

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

[YoussefBelloum]

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]

[sunny_talwar]

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

[YoussefBelloum]

‌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‌