Excel number to date conversion

N

Neville

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.
 
J

James W.

Try this formula...

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

if A1 contains your date
 
G

Gord Dibben

Neville

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

Gord Dibben Excel MVP
 
N

Neville

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.
 
M

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.
 
Top