Diane,

I'm not sure if 60501 means 1st May 2006 or 5th January 2006, but you
can extract the constituent parts by means of string functions. If the
number is in A2, then

=RIGHT(A2,2) will extract "01"
=MID(A2,2,2) will extract "05"
=LEFT(A2,1) will extract "6"

You could put these back together in a variety of ways to get a date -
I use this one:

=VALUE(RIGHT(A2,2)&"/"&MID(A2,2,2)&"/"&LEFT(A2,1))

and that gives me 1st May 2006 with my settings. Format the cell with
the formula in with an appropriate date format, then copy down for 2000
rows.

Hope this helps.

Pete

LDL wrote:
I am transferring data from an AS400 into Excel. The date field comes into
Excel like this: 60501. This date is actually 05-01-06. Just like Excel,
the AS400 suppresses the leading 0 in the date. We cannot get the date data
to come into Excel any differently. I need to convert this number into a
date but don't know how. I don't know if you can create 1 formula to do
this or create multiple formula's to do it. The concatenate function allows
me to place a 0 in front of the 6. That gets me all the numbers needed for
a date, but now I need to swap the numbers around and separate into
mm/dd/yy. My ultimate goal is to calculate the number of workdays between 2
dates. I have over 2,000 rows of data and was hoping for an automated way
to do this every month. Are there any genius's out there that can help?
Thanks, Diane