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))