Parsing non-standard date format in Excel cell

Question

I want to convert dates formatted like "March 30th 2017, 05:00:00.000" to an excel date value? What's the most elegant solution I can do this with using a cell-formula and not VBA?

Answer 1

Nested IFERROR functions can handle the variety of number ordinals.

=--SUBSTITUTE(REPLACE(A2, IFERROR(FIND("st ", A2),IFERROR(FIND("nd ", A2),IFERROR(FIND("rd ", A2), IFERROR(FIND("th ", A2), LEN(A2))))), 3, ", "), ", ", " ",2)

I used a custom number format of [Color10]mmmm dd, yyyy hh:mm:ss.000;;;[Color3]@. Beyond the fact that the text is left-aligned and the true dates are right-aligned, this will put text-that-look-like-dates in a red font and true dates in a green font.

Answer 2

This will do the standard "rd","th","st","nd"

=--(LEFT(A1,MIN(FIND({"rd","th","st","nd"},A1 & "thrdstnd"))-1)& ", " & SUBSTITUTE(MID(A1, MIN(FIND({"rd","th","st","nd"},A1 & "thrdstnd"))+2,LEN(A1)),",",""))

You can add other suffixes as you need to the formula

Then you can format it as you like.

Answer 3

If you want a true date and want to drop the time part then:

=DATEVALUE(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1),"th",","))

You will need to nest SUBSTITUTE() functions to handle "nd" and "st" ordinals if you have them:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1),"th",","),"nd",","),"st",","))