Short Date Formatting

[Majikwoman]

Our user community is having trouble with formatting a cell to use the short
date. If they format the date to be for instance 06/20/08 and then type in
062008 a completely off the wall date appears such as 10/12/69. Is anyone
else experiencing this issue or does anyone have a fix for this? Please help.

 

[Joel]

I know the answer. The date that show is 10/7/2069, the same thing happens
on my PC. When you enter 062008 ity is being entered as the number 62008 and
converted to a date format. 62008 is the number of days from Jan 1, 1900 to
10/7/2069.

Excel stores a date as a number. the number 1 = Jan 1, 1900. Each day is
counted as the number 1. Each hour is counted as 1/24 (1 day divided by 24
hours). Each minute is 1/(24*60).

Excel doesn't recognize 062008 as a date format, instead it thinks it is a
number. Because you have the cell formated as a date it converts the entry
into a date.

 

[Majikwoman]

But there is an error in your theory ... entering this date yesterday would
have worked. That is what I don't understand. It does the same thing in
2003 and 2007. You idea is great but why did it work before>

 

[Joel]

I tried a lot of different dates and they all work the same way. Are you
implying when you said "that it would of worked yesterday" that a update was
performed to excel to stop it from working, or do you mean if you put
yesterdays date (061908) that it would work. I tried 061908 and it does the
same thing.

 

[Majikwoman]

No the she was able to enter the date yesterday in that format and it would
work. But today that format does not work. No updates were done ... it is a
mystery to me as to why it would work one day and not the next. I am at a
loss.

 

[Joel]

You don't know how excel was programmed to be able to say it is a mystery.
Lots of programming errors exist in microsoft products that arre mysteries
why one thing works and another doesn't. I'm going to tried this on my
compute at home a let you know how my home PC works. I know for sure that no
updtes wre perfromed. At a company you don't really know when an update is
performed. I have my home PC set with automtic updates from microsoft
disabled.

 

[Ron Rosenfeld]

Our user community is having trouble with formatting a cell to use the short
date. If they format the date to be for instance 06/20/08 and then type in
062008 a completely off the wall date appears such as 10/12/69. Is anyone
else experiencing this issue or does anyone have a fix for this? Please help.

Date formatting only affects how the date is displayed. It has NOTHING to do
with how you may enter valid dates.

Dates may be entered in the system format. Start/Control Panel/Regional and
Language settings will show this format. Unambiguous dates will also be
interpreted properly in Excel -- e.g. 5 jun 2008 will be interpreted as a date
in most, if not all, English versions of Excel.

So 062008 is interpreted as just a number by Excel's input agent. Since you
have the cell formatted as a date, it will display the date that corresponds to
serial number 62,008 on your system, which should be 7 Oct 2069 if you are
using the 1900 date system.

--ron

 

[Gord Dibben]

All versions of Excel have never allowed nor recognized dates being entered
without a separator of some sort.

.. or / or -

"She" is incorrect when she says she did it yesterday.

To quick enter dates as 061908 to get June 19, 2008 you must use VBA code or
a helper cell with a formula.

See Chip Pearson's site for quickentry code.

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


Gord Dibben MS Excel MVP

 

[Joel]

I just verified on my home PC which hasn't been updated in a few weeks that
it doesn't work. Gord, you are right. I wasn't sure becuase they did a push
last night on my PC with some updates and didn't want to say something wrong
unless I proved it on my home PC.