B1: =DATE(RIGHT(A1,4),MONTH("1 "&LEFT(RIGHT(A1,12),4)),LEFT(RIGHT(A1,8),2))+
LEFT(A1,8)
With
A1: containing time/date text like your sample
example: 8:26 am TUE FEB 24, 2009
Try this (formatted as yyyy/mm/dd hh:mm):
Code:B1: =DATE(RIGHT(A1,4),MONTH("1 "&LEFT(RIGHT(A1,12),4)),LEFT(RIGHT(A1,8),2))+ LEFT(A1,8)
In the above example, the formula returns: 2009/02/24 08:26
Is that something you can work with?
Try eg:
=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,12),LEFT(RIGHT(A1,12),4),""),","," " &LEFT(RIGHT(A1,12),3))+LEFT(A1,FIND(" ",A1,7)-1)
formatted as yyyy/mm/dd hh:mm.
Jim,
The formula:
=DATEVALUE(RIGHT(A1,12))
doesn't work if the regional settings are eg English(United Kingdom). That's why I had to turn it into 24 FEB 2009.