Rearranging Data Within a Cell
 

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

Rearranging Data Within a Cell
 

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

Rearranging Data Within a Cell
 

Well, you could split the 60501 up into three columns and then re-concatenate
them in the correct order if you want:

=LEFT(A1,1) returns 6
=MID(A1,2,2) returns 05
RIGHT(A1,2) returns 01

Then you could do your concatenation from those extracted values.

Dave
--
Brevity is the soul of wit.


"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

Rearranging Data Within a Cell
 

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

Rearranging Data Within a Cell
 

With my USA settings and 60501 in A1:
=--TEXT(20000000+A1,"0000\/00\/00")
Gave me May 1st, 2006




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

--

Dave Peterson

Rearranging Data Within a Cell
 

Thank you all for your responses. I knew there were genius's out there!
Diane

"Dave Peterson" wrote in message
...
With my USA settings and 60501 in A1:
=--TEXT(20000000+A1,"0000\/00\/00")
Gave me May 1st, 2006




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

--

Dave Peterson