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) ) | 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) ) | 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]") ) | 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.
No comments:
Post a Comment