Format Excel Date

[KWHITEHURST]

what i thought would be a straightforward piece of code, turns out not to be.  No matter what formula i use on Qlik Load i cannot get the date from Excel to format correctly

In Excel the date field is formatted as custom i.e.

  20/01/2024 13:30:00
  20/01/2024 00:00:00

i am wanting just 20/01/2024 but to be recognised as date.  

i've tried:

    Date(Floor(TimeStamp#(IncidentDate, 'DD/MM/YYYY hh:mm:ss TT'))) as LC_DATE

   date(IncidentDate,'DD/MM/YYYY') as LC_DATE
   date(date#(IncidentDate,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as LC_DATE

it either comes back blank or on the surface looks as if it's worked, but showing as two lines i.e.

  20/01/2024
  20/01/2024

i'm sure there's someone really smart on this forum who can help me please 🙂

[Or]

If the Excel field is a date, you don't need any conversions - you would use something like DayName(IncidentDate) or Date(Floor(IncidentDate)).

If it's a text string, you would need to parse it using timestamp#() first, perhaps:

Dayname(Timestamp#(IncidentDate,'DD/MM/YYYY hh:mm:ss))

[KWHITEHURST]

thank you, unfortunately that's not working it's coming back as blank

[Or]

Works fine when I test it on my end...

You could also try Date#(Left(IncidentDate,10),'DD/MM/YYYY')

Anything that returns two rows is doing so because you haven't actually done anything to eliminate the time part of the timestamp (which is where either DayName() or Floor() come into play).

[KWHITEHURST]

thanks i did try that one, it comes back as numeric i.e. 45311.5625 or 45311

sorry 😞

[Or]

Those are the numbers representing a date. If you get 45311, all you need to do is apply date(). If you get the fraction part, you need to get rid of it - that part represents the time.

See e.g. https://support.microsoft.com/en-gb/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252

[KWHITEHURST]

thanks

I have tried 

Date(Date#(Left(IncidentDate,10),'DD/MM/YYYY')) as 'IncDate' now blank

[qv_testing]

Try this

Date(floor(Date#([field_name], 'DD/MM/YYYY h:mm:ss')),'YYYY-MM-DD') as NewDate;

[Or]

It sounds like your field is already a date and does not need converting, in which case you just need to use DayName() or Date(Floor()) as per my original post.

As I said, all of these work for me using an Excel file matching your description, so if they don't work for you, perhaps the underlying file isn't as described...

[KWHITEHURST]

unfortunately to no avail:

Date(floor(Date#([IncidentDate], 'YYYY-MM-DD h:mm:ss')),'YYYY-MM-DD') as IncDateTest

below is what is in the Excel workbook - date formatted as Custom: