Format date in a cell

[Mary Put]

How do I format cells so that when I put in a number it automatically changes
it to a date? I set up the cells using Format-Number-Date-03/14/01. But
when I type in 101408 the cell shows 08/22/77. What am I doing wrong??

 

[Mike H]

Hi,

Dates in Excel are numbers and the number 101408 is August 22 2077 so Excel
is doing exactly what your telling it to do. Without resorting to VB I don't
know of any way to make it recognise that number as the date you believe it
is. can't you enter delimeters 10/14/08

Mike

 

[David Biddulph]

You forgot to type in the slants.

What you've got is the 101408th day in Excel's calendar, which is 22nd
August 2177.
If you've got a lot of data where you forgot the slants, you can convert
with Data/ Text to Columns if you tell it (at the 3rd stage of 3 in the
wizard) that you want the data to be interpreted as dates in MDY format.

 

[David Biddulph]

Dates in Excel are numbers and the number 101408 is August 22 2077 ...

2177 , not 2077 ?

...so
Excel is doing exactly what your telling it to do. Without resorting
to VB I don't know of any way to make it recognise that number as the
date you believe it is. ...

Data/ Text to Columns, Mike.

 

[Mike H]

Agreed, only a mere hundred years out

2177 , not 2077 ?


Data/ Text to Columns, Mike.