Convert date format

[#1]

Hello,

I need to convert dates from Tue Nov 24 14:36:20 CET 2009

to something like 24.11.2009

Date and date# is not working as expected...

Any ideas?

Thanks in advance,

Eva

[Miguel_Angel_Baeyens · Accepted solution]

Hello Eva,

Unless any other has some cleaner (and above all, shorter) expression, I'd do some cleanup to get the proper timestamp:

=Date(Date#(Right(Replace('Tue Nov 24 14:36:20 CET 2009', Mid('Tue Nov 24 14:36:20 CET 2009', Index('Tue Nov 24 14:36:20 CET 2009', ' ', -2), Len('Tue Nov 24 14:36:20 CET 2009') - Index('Tue Nov 24 14:36:20 CET 2009', ' ', -1)), ''), Index('Tue Nov 24 14:36:20 CET 2009', ' ', -2)), 'MMM DD hh:mm:ss YYYY'), 'DD.MM.YYYY')

I'm likely missed some commas or closed parentheses.

[#3]

Hi Miguel,

it works 🙂 thanks a lot for your help!

Regards,

Eva

[suniljain]

Replace String By Your Field Name

=Date(Makedate(Trim(subfield('Tue Nov 24 14:36:20 CET 2009',' ',6)), Num(Month(date#(subfield('Tue Nov 24 14:36:20 CET 2009',' ',2),'MMM'))), Trim(subfield('Tue Nov 24 14:36:20 CET 2009',' ',3))),'DD.MM.YYYY')

[rwunderlich]

How about

=date(
date#(
subfield('Tue Nov 24 14:36:20 CET 2009',' ',2)
& subfield('Tue Nov 24 14:36:20 CET 2009',' ',3)
& subfield('Tue Nov 24 14:36:20 CET 2009',' ',6)
,'MMMDDYYYY')
)