Date Format

[readystate]

I have a spreadsheet that contains a list of dates but they are formatted as
follows - 20100427. How would I go about converting those cells to a
standard format - 04/27/2010? Any help or suggestions would be appreciated.

 

[Jim Thomlinson]

Use a formula such as this to convert it to a date

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

you can then format the date however you wish using standard XL formats.

 

[readystate]

Thanks...worked fine.

Use a formula such as this to convert it to a date

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

you can then format the date however you wish using standard XL formats.

 

[Gord Dibben]

In place with no formulas..............

Data>Text to Columns>Next>Next>Column Data Format>YMD and Finish.


Gord Dibben MS Excel MVP