Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
qlikfresh
Contributor III
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!

qlikfresh_0-1631695535940.png

 

Labels (2)
1 Solution

Accepted Solutions
AshutoshBhumkar
Partner
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

View solution in original post

6 Replies
AshutoshBhumkar
Partner
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

qlikfresh
Contributor III
Contributor III
Author

Hey @AshutoshBhumkar 

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

qlikfresh_0-1631696133986.png

 

AshutoshBhumkar
Partner
Partner

Try using this 

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

) as Date

qlikfresh
Contributor III
Contributor III
Author

Hi @AshutoshBhumkar 

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!

AshutoshBhumkar
Partner
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

View solution in original post

qlikfresh
Contributor III
Contributor III
Author

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