Excel won't read number as a date. Please Help

[scany42]

I receive a document from a customer, for whatever reason they enter the dates as a number. i.e. 20130604 for June 4th 2013. I need excel to recognize this number as a date. Formatting the cell as a date does nothing, I tried playing around with the custom format but I couldn't get it to work. Any ideas?

I use excel 2010. Thanks

 

[ChrisM]

Use the Date formula in combination with Left/Mid/Right text operations

=Date(Left(A1,4),Mid(A1,5,2), Right(A1,2))

This assumes that ALL of your dates are 8 characters long and in YYYYMMDD format.

 

[Akashwani]

Hi and welcome to MrExcel.

Try this Custom number format...

0000-00-00

I hope that helps.

Ak

 

[Weazel]

Just because I think its a cool way of doing it....courtesy of shg and Rick I believe

=--TEXT(a1,"00\/00\/00") formatted as m/d/yyy

 

[Dave Patton]

another alternative

Data Text to Columns Date