New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Contributor III

Mixed Dates in Field

Hi Community,

I have a question about converting a date field which has a mixed format

If you look at the screenshot below, DATE_SAMPLEDoriginal is mixed with MM/DD/YYYY hh:mm format & numerical format

What is the best way to convert this to DD/MMM/YYYY format?

Thanks!

Labels (2)

• Formules

1 Solution

Accepted Solutions
Partner

Okay, you can add this then

Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY') in alt()

like :

Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')) as Date1

6 Replies
Partner

Hi, You can use ALT() for mixed date formats.

e.g

Alt(
Date(Timestamp#(Date,'MM/DD/YYYY hh:mm'),'DD/MMM/YYYY'),
Date(Date#(Date),'DD/MMM/YYYY')
) as Date

Thanks,

Ashutosh

Contributor III
Author

Thanks for the quick reply, I tried that formula and looks like its not working for the numerics

Partner

Try using this

Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')

) as Date

Contributor III
Author

Nearly there! It looks like it is converting it the wrong way round, it reads 09-Jan-2021 but should say 01-Sep-2021. Is there a way I can flip the month and day?

Thanks!

Partner

Okay, you can add this then

Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY') in alt()

like :

Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')) as Date1

Contributor III
Author

@AshutoshBhumkar , thank you 🙂 I would not have gotten that on my own. Much appreciated

Tags
Community Browser