
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Format Excel Date
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 🙂
- « Previous Replies
-
- 1
- 2
- Next Replies »


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank you, unfortunately that's not working it's coming back as blank


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks i did try that one, it comes back as numeric i.e. 45311.5625 or 45311
sorry 😞


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks
I have tried
Date(Date#(Left(IncidentDate,10),'DD/MM/YYYY')) as 'IncDate' now blank

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Date(floor(Date#([field_name], 'DD/MM/YYYY h:mm:ss')),'YYYY-MM-DD') as NewDate;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:

- « Previous Replies
-
- 1
- 2
- Next Replies »