Excel: Converting String Date to Date Type

This post demonstrates how to convert a string-formatted date to a Date type using three Excel formulas.

Converting String Date to Date Type

This method is a time-saving solution for processing date columns represented as strings like '12-Nov-2022'. Crafting an Excel formula to convert these strings to '2022-11-12' each time can be laborious and time-consuming. It's a common task to handle when preprocessing daily financial data in empirical analysis.

Excel formula

Given a target cell B2 with the value like '14-Jun-1961', a first Excel formula to convert this date string to the 'yyyy-mm-dd' date format is as follows.

=DATE( RIGHT(B2,4), IF(MID(B2,4,3)="Jan","01",IF(MID(B2,4,3)="Feb","02", IF(MID(B2,4,3)="Mar","03",IF(MID(B2,4,3)="Apr","04", IF(MID(B2,4,3)="May","05",IF(MID(B2,4,3)="Jun","06", IF(MID(B2,4,3)="Jul","07",IF(MID(B2,4,3)="Aug","08", IF(MID(B2,4,3)="Sep","09",IF(MID(B2,4,3)="Oct","10", IF(MID(B2,4,3)="Nov","11",IF(MID(B2,4,3)="Dec","12", "")))))))))))), MID(B2,1,2) ) Colored by Color Scripter

cs

A second, more compact Excel formula to achieve the same date conversion is:

=DATE( RIGHT(B2,4), MATCH(MID(B2, 4, 3),     {"Jan","Feb","Mar","Apr","May","Jun",      "Jul","Aug","Sep","Oct","Nov","Dec"}, 0), MID(B2,1,2) ) Colored by Color Scripter

cs

A third, more robust Excel formula is:

=DATE( FILTERXML("<root><item>" & SUBSTITUTE(B2, "-",      "</item><item>") & "</item></root>", "//item[3]"), MATCH(FILTERXML("<root><item>" & SUBSTITUTE(B2, "-",      "</item><item>") & "</item></root>", "//item[2]"),     {"Jan","Feb","Mar","Apr","May","Jun",      "Jul","Aug","Sep","Oct","Nov","Dec"}, 0), FILTERXML("<root><item>" & SUBSTITUTE(B2, "-",      "</item><item>") & "</item></root>", "//item[1]") )   Colored by Color Scripter

cs

Result

We can observe the results of the three Excel formulas, and the third formula stands out for its robustness in dealing with variable day lengths, which are often encountered.