Format date in a cell
 

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??

Format date in a cell
 

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

"Mary Put" wrote:

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??

Format date in a cell
 

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

Mary Put wrote:
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??

Format date in a cell
 

Mike H wrote:

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.
--
David Biddulph

Format date in a cell
 

Agreed, only a mere hundred years out :)

"David Biddulph" wrote:

Mike H wrote:

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.
--
David Biddulph