How do i change the date 6302000 to 6/30/2000 ?

[Guest]

I want to format date cells from 6302000 to 6/30/2000. Could you please help
with this?

 

[CLR]

=LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4) will do the sample date you have
given,
but we need a little more info to be able to help more.........do you have
many "dates" like this to do?.........do the single digit months have
leading zeros? are there double digit months in your data of
interest?..........are the days always two digits?

Vaya con Dios,
Chuck, CABGx3

 

[Govind]

Hi,

If the value 6302000 is in Cell A1, You can use this formula in B1

=TEXT(DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2,2)),"mm/dd/yyyy")

This will work as long as the length of the date cells are constant.If
the length of the date cells are not constant, then use this

=IF(LEN(A1)=7,TEXT(DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2,2)),"mm/dd/yyyy"),TEXT(DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)),"mm/dd/yyyy"))

Regards

Govind.

 

[Aladin Akyurek]

=SUBSTITUTE(TEXT(A1,"00-00-0000"),"-","/")+0