Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Hey @AshutoshBhumkar
Thanks for the quick reply, I tried that formula and looks like its not working for the numerics
Try using this
Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')
) as Date
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!
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
@AshutoshBhumkar , thank you 🙂 I would not have gotten that on my own. Much appreciated