Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Enter data and Function in same cell | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Cell data format | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions |