have date show in cell when only entering numbers
 

Using Excel 2003 on WinXP. Have chosen to format the cell as a date (in
mm/dd/yyyy format). When entering only numbers into the cell, the "date"
that displays is in no way the actual date. So, when I enter 72508, the cell
displays 07/07/2098. When I enter 7/25/08, the cell displays 7/25/2008. Is
there a way to make the cell show a date in mm/dd/yyyy format and only enter
numbers and not include the dashes or hash marks when entering the numbers?
I know that it works when entering phone numbers (if you've formatted the
cell to display phone numbers - you enter 5558885555 and get (555) 888-5555.)
Thanks in advance.

have date show in cell when only entering numbers
 

That is because a day in Excel = 1 and Excel AD is Jan 0 1900 so today
equals
39653 days after Jan 0 1900 thus when you enter 72508 in a cell it means
Excel thinks it is 72508 days after Jan 0 1900 which equals 07/07/2098.
That's the reason. So you can't enter it like that unless you have an event
macro that will convert it to a real date when you press enter.

http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

"cjlatta" wrote in message
...
Using Excel 2003 on WinXP. Have chosen to format the cell as a date (in
mm/dd/yyyy format). When entering only numbers into the cell, the "date"
that displays is in no way the actual date. So, when I enter 72508, the
cell
displays 07/07/2098. When I enter 7/25/08, the cell displays 7/25/2008.
Is
there a way to make the cell show a date in mm/dd/yyyy format and only
enter
numbers and not include the dashes or hash marks when entering the
numbers?
I know that it works when entering phone numbers (if you've formatted the
cell to display phone numbers - you enter 5558885555 and get (555)
888-5555.)
Thanks in advance.

have date show in cell when only entering numbers
 

Thanks for this info. I will work with my user to see if this is what she
wants to do. I will come back and rate the reply at that time.


"Peo Sjoblom" wrote:

That is because a day in Excel = 1 and Excel AD is Jan 0 1900 so today
equals
39653 days after Jan 0 1900 thus when you enter 72508 in a cell it means
Excel thinks it is 72508 days after Jan 0 1900 which equals 07/07/2098.
That's the reason. So you can't enter it like that unless you have an event
macro that will convert it to a real date when you press enter.

http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

"cjlatta" wrote in message
...
Using Excel 2003 on WinXP. Have chosen to format the cell as a date (in
mm/dd/yyyy format). When entering only numbers into the cell, the "date"
that displays is in no way the actual date. So, when I enter 72508, the
cell
displays 07/07/2098. When I enter 7/25/08, the cell displays 7/25/2008.
Is
there a way to make the cell show a date in mm/dd/yyyy format and only
enter
numbers and not include the dashes or hash marks when entering the
numbers?
I know that it works when entering phone numbers (if you've formatted the
cell to display phone numbers - you enter 5558885555 and get (555)
888-5555.)
Thanks in advance.

have date show in cell when only entering numbers
 

Here's a link on how to install a macro

http://www.mvps.org/dmcritchie/excel/install.htm



--


Regards,


Peo Sjoblom

"cjlatta" wrote in message
...
Thanks for this info. I will work with my user to see if this is what she
wants to do. I will come back and rate the reply at that time.


"Peo Sjoblom" wrote:

That is because a day in Excel = 1 and Excel AD is Jan 0 1900 so today
equals
39653 days after Jan 0 1900 thus when you enter 72508 in a cell it means
Excel thinks it is 72508 days after Jan 0 1900 which equals 07/07/2098.
That's the reason. So you can't enter it like that unless you have an
event
macro that will convert it to a real date when you press enter.

http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

"cjlatta" wrote in message
...
Using Excel 2003 on WinXP. Have chosen to format the cell as a date
(in
mm/dd/yyyy format). When entering only numbers into the cell, the
"date"
that displays is in no way the actual date. So, when I enter 72508,
the
cell
displays 07/07/2098. When I enter 7/25/08, the cell displays
7/25/2008.
Is
there a way to make the cell show a date in mm/dd/yyyy format and only
enter
numbers and not include the dashes or hash marks when entering the
numbers?
I know that it works when entering phone numbers (if you've formatted
the
cell to display phone numbers - you enter 5558885555 and get (555)
888-5555.)
Thanks in advance.