MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Parsing Date String


November 07, 2001 - by Bill Jelen

Dan asks:

I keep having a problem converting numbers into dates. I have the date 19960105 for example, but excel doesn't recognize it as a date without me having to put two slashes into it (i.e. 1996/01/05)... Is there any formula that I can use that will prevent me from having to put in all those slashes?

One option is using a formula. The Date() function allows you to specify a date by giving the Year, Month, Day. The following formula with parse your value and convert it to a date:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))


Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.