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 🙂

[Nagaraju_KCS]

format should be 

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

[KWHITEHURST]

still returning blank 😞

[vinieme12]

Try below

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

[KWHITEHURST]

again blank, it's bizarre, nothing seems to work

[Nagaraju_KCS]

This is working for me 

Data:
LOAD Distinct Date(floor(Date#([IncidentDate], 'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') as NewDate;
LOAD * INLINE [
IncidentDate
20/01/2024 13:30
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
];