Rearranging Data Within a Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LDL - 23 Oct 2006 18:58 GMT

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

Reply to this Message

Pete_UK - 23 Oct 2006 19:14 GMT

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

> 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,
[quoted text clipped - 8 lines]
> to do this every month. Are there any genius's out there that can help?
> Thanks, Diane

Reply to this Message

Dave F - 23 Oct 2006 19:16 GMT

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

Signature

Brevity is the soul of wit.

Reply to this Message

Edward - 23 Oct 2006 19:32 GMT

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

Reply to this Message

Dave Peterson - 23 Oct 2006 19:32 GMT

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

Signature

Dave Peterson

Reply to this Message

LDL - 24 Oct 2006 15:37 GMT

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

> With my USA settings and 60501 in A1:
> =--TEXT(20000000+A1,"0000\/00\/00")
[quoted text clipped - 19 lines]
>> to do this every month. Are there any genius's out there that can help?
>> Thanks, Diane

Reply to this Message