Microsoft Excel – How to Parse a Nonstandard Date Format

datemicrosoft excel

I have a column of date-like string values in the format yyyy-mm-dd, such as 2011-Sep-13. I need to convert these to Excel date serial numbers so that I can use them in formulas.

DATEVALUE isn't able to recognize this format; I just get #VALUE! when I try. It also does not take a custom date format (unlike most programming languages).

How can I convert arbitrary date formats into Excel date values?

Best Answer

You'll need to do string parsing, then pass an appropriate formatted value into DATEVALUE – something like this:

=DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4))

(edit: updated formula - tested and it works for me on one example - may need to refine depending on your input)

Related Question