Convert number to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
data>text to columns, click next twice in step 3 under column data format
select date and make sure it is MDY, then click finish
 
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
 
If you need a genuine date then:

=DATE(RIGHT(A1,2)+2000,INT(A1/10000),RIGHT(INT(A1/100),2))
 
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,
 
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 menu>data>text 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,
 
Awesome!! Perfect! Thank you for your help :)

PCLIVE said:
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
Click to expand...
 
Back
Top