Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date column in my sheet which is in "Mon Nov 23 08:22:02 CST 2001" format.
I want to convert and display it in DD/MM/YYYY format.
its need to break in date Month and Year
simpaly create date using
MakeDate(Right(Datefield,4),
Month(Date(Date#(Mid(Datefield,5,3),'MMM'),'MM')),
Mid(Datefield,8,3))
event you could format this using
Date(MakeDate(Right(Datefield,4),
Month(Date(Date#(Mid(Datefield,5,3),'MMM'),'MM')),
Mid(Datefield,8,3)),'Format')
here format is dd/MM/YY or DD-MM-YYY or YYYY-MM-DD or DD-MMM-YYYY
as per your convineyance
=Date(Floor(Timestamp#(Replace(Mid('Mon Nov 23 08:22:02 CST 2001',5),'CST ',''),'MMM DD hh:mm:ss YYYY')),'DD/MM/YYYY')
for the date part, or
=Timestamp(Timestamp#(Replace(Mid('Mon Nov 23 08:22:02 CST 2001',5),'CST ',''),'MMM DD hh:mm:ss YYYY'))
for a complete timestamp.