Dat Formatting
 

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.

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


"Mike Busch" wrote in message
...
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.

Mike

DataText to ColumnsNextNext. Column Data FormatDateYMD and Finish.

Your copied sample came out as desired.


Gord Dibben Excel MVP

On Fri, 18 Feb 2005 15:21:02 -0800, "Mike Busch"
wrote:

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.

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.



Mike Busch wrote:

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.

--

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.

Mike Busch wrote:

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.

--

Dave Peterson