Forum Discussion
Solved
converting text string to date time format
hi I would like to be able to sort some data I get by date and time, however the date field is expressed like so: May 15, 2023, 6:34:54 PM how can i convert this to a date time that excel recog...
Assuming that all values use abbreviated month names Jan, Feb, etc. (May in your example is ambiguous!), try
=CONCATENATE(MID(F2,5,2),"",MID(F2,1,3),"-",MID(F2,9,4))+TRIM(RIGHT(F2,11))
Format the cell(s) with the formula as date and time.
You can also use Power Query (Data > From Table/Range). It will automatically convert the values to real date/time values.
Assuming the day/month order is valid with your computer settings it is only the explicit commas that cause trouble, so
= LET(
cleaned, SUBSTITUTE(text, ",", ""),
DATEVALUE(cleaned) + TIMEVALUE(cleaned)
)should return a valid datetime to be formatted any way you choose. The defined name 'text' refers to the entire range of text dates to be converted.