Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
I am importing data from an AS400 program and my dates are formated as 10107
for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
datatext to columns, click next twice in step 3 under column data format
select date and make sure it is MDY, then click finish -- Regards, Peo Sjoblom "Teri" wrote in message ... I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
Certainly there must be another way, but here's one way. Format your cells
as Date and the desired format. =(LEFT(LEFT(A1,LEN(A1)-2),LEN(LEFT(A1,LEN(A1)-2))-2)&"/"&RIGHT(LEFT(A1,LEN(A1)-2),2)&"/"&RIGHT(A1,2))+0 "Teri" wrote in message ... I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
If you need a genuine date then:
=DATE(RIGHT(A1,2)+2000,INT(A1/10000),RIGHT(INT(A1/100),2)) -- Gary''s Student - gsnu200723 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
Hi,
right click on the cell you want to format choos format cells and then choos number tab and then choos custom and then enter in the type textbox mmddyy but it formats Jan 1, 2007 to 010107 not 10107. Thanks, -- Farhad Hodjat "Teri" wrote: I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
Did you try that? It will not work
-- Regards, Peo Sjoblom "Farhad" wrote in message ... Hi, right click on the cell you want to format choos format cells and then choos number tab and then choos custom and then enter in the type textbox mmddyy but it formats Jan 1, 2007 to 010107 not 10107. Thanks, -- Farhad Hodjat "Teri" wrote: I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
Hi again,
it was a missunderestood try this: A B 1 10107 =IF(LEN(A1)=5,"0"&A1,LEFT(A1,6)) 2 110107 enter above formula in the cell B1 ( in this example ) and the copy drag the formula to where you need and then copy past special value the formula that you copied and then go to menudatatext to column and then choos Delimited and next and coos other and enter / in the text box and next and choos MDY from drupdown menu and Finish. Thanks, -- Farhad Hodjat "Teri" wrote: I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert number to date
Awesome!! Perfect! Thank you for your help :)
"PCLIVE" wrote: Certainly there must be another way, but here's one way. Format your cells as Date and the desired format. =(LEFT(LEFT(A1,LEN(A1)-2),LEN(LEFT(A1,LEN(A1)-2))-2)&"/"&RIGHT(LEFT(A1,LEN(A1)-2),2)&"/"&RIGHT(A1,2))+0 "Teri" wrote in message ... I am importing data from an AS400 program and my dates are formated as 10107 for January 1, and would be 110107 for November 1. Can anyone help me figure out out to convert to 1/01/07 and 11/01/07? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you convert a number into a date | Excel Worksheet Functions | |||
Convert number to date | Excel Discussion (Misc queries) | |||
Convert Date to number? | Excel Worksheet Functions | |||
How can I convert a date into a week number... | Excel Worksheet Functions | |||
How do I convert Date serial number to date | Excel Worksheet Functions |