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

If you want to code for two digit years as well, you can extract the
year like this:

=TRUNC(A1,-4)/10000

or to fully convert A1 to a date:

=DATE(TRUNC(A2,-4)/10000+2000,(TRUNC(A2,-2)-TRUNC(A2,-4))/100,A2-TRUNC(A2,-2))

Edward