Excel number to date conversion

[Guest]

In excel how do you convert a number ie. 960730 to the date 30-07-96, excel
help is no use and when I try to format it as a date it comes up with may
4025 or something.

 

[Guest]

Try this formula...

=RIGHT(A1,2) & "-" & MID(A1,3,2) &"-"&LEFT(A1,2)

if A1 contains your date

 

[JWolf]

=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
custom format cell to mm-dd-yy

 

[Gord Dibben]

Neville

Data>Text to Columns>Next>Next "Column Data Format">Date YMD and Finish

Gord Dibben Excel MVP

 

[Guest]

Thanks for your help, however now a I have found someone has entered dates as
1000104 for 04/01/00, no idea why the 1 is in from, is there any way i can
convert this.

 

[Myrna Larson]

If you study the formula that you were given, you should be able to figure out
how to modify it to handle (i.e. eliminate) the leading 1.

I expect the person added it so that the 2000 data would sort after the 1999
data.