Dat Formatting

M

Mike Busch

I received a large ASCII format document that I imported into Excel. The date
appears as 19500323. Is there anyway to format this to read 03/23/1950.
thanks in advance.
 
T

Trevor Shuttleworth

Mike

you'll probably need an extra column. Assuming the date is in cell A1, the
formula would be:

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

or, simply:

=MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

Note that the first formula returns a date *value* which is formatted as a
date. The second returns a text string that *looks* like a date. So, the
first gives the number 18345 which is then formatted as a date to give
03/23/1950.

Regards

Trevor
 
G

Gord Dibben

Mike

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

Your copied sample came out as desired.


Gord Dibben Excel MVP
 
D

Dave Peterson

One column at a time:

Select that column
Data|text to columns
choose YMD as the field type
Format|cells|number tab
and give it the format you like best.
 
D

Dave Peterson

Ps. If you're importing this file (like File|open), you can choose YMD for that
field and save a little bit of time.
 
Top