Convert Number to Date

[rwmill9716]

What is the formula to convert a number like 122114 to 12/21/14 and 13015 to 01/30/15?

Thanks

Ric

 

[AlphaFrog]

What is the formula to convert a number like 122114 to 12/21/14 and 13015 to 01/30/15?

Thanks

Ric

Try this...
=TEXT(A1,"00-00-00")*1
Format the formula cells as Date.

This assumes the "day" value always has a two digits (a leading zero for single digit days). Otherwise it doesn't know if
12915 is 1/29/2015 or 12/9/2015

 

[Trouttrap2]

try this
=TEXT(A1,"00\/00\/00")

 

[azumi]

=DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&(RIGHT(A1,2)))
and formatted as date

The second date could be problem when have 5 digits, try to add apostrophe " ' " when you have 01 or 02 and so on

 

[SpillerBD]

ADVERTISEMENT

Accounting for the possibility of preceding zeroes....
=(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))

 

[rwmill9716]

Thanks, Trouttrap2 and AlphaFrog, these both work fine.

Ric

 

[SpillerBD]

Accounting for the possibility of preceding zeroes....
=(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))

oops.. that should be
=DATEVALUE(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))