On Wed, 20 Dec 2006 11:45:00 -0800, ryan
wrote:

so 1) everytime i enter just the year as in 2005, after leaving the cell,
it's changed to 6/27/1905. everytime i enter 2001, after leaving the cell,
it's changed to 6/23/1905. what's going on? and what do i have to do to
permanately change this?

2) when i enter just the month and the year as in 5/05, or even 5/2005, it
is changed to 1-May, but up in the formula box or whatever it is called, it
says 5/1/2006. it doesn't matter what month or what year, if i use the m/yy
or m/yyyy format, it always changes it to the first of the month of 2006.
again, what do i have to do to permenately change this?

in both cases, it doesn't matter if a copy and paste the date in or type it
in, and sometime (keyword: sometimes) the format painbrush works.

please, what is going on?

What is going on is that the cell format only controls how the entry is
DISPLAYED. For entering sequences that look like dates, Excel supplies a
different set of rules then for entering numbers.

For example, when you enter 2005 into a cell that is formatted as a date, Excel
stores 2005 in that cell. It will display the 2005th day of its "calendar"
which starts at 1/1/1900 which turns out to be 27 june 1905.

When you enter a sequence such as 5/05, Excel tries to interpret this as a date
in the Windows regional format. If you are set to US (m/d/y), it will try to
interpret as month/day and append the current year. If the "d" cannot be in
the month (e.g. 2/30), then it will interpret the denominator as a year, and
translate from a two digit to a four digit year in accord with the setting
determining that.

If the numerator is not a valid month, (ie not 1-12), then the entire entry
will be interpreted as text.

The above parsing takes place entirely independent from how you format the
cell.
--ron