Why does Excel interpret 2/29 as Feb 1st?

J

Jo

I have the cells formated to show the dates as "2-Feb" for instance. But
when I type in 2/29, Excel converts it to "1-Feb". The only way I can bet it
to accept 2/29 is to format the cells as text. What am I doing wrong?
 
A

Arvi Laanemets

Hi

Try to type in 2005/2/29 (or whatever is the date format you use)


Arvi Laanemets
 
P

Peo Sjoblom

Because there is no Feb 29 2005, since you don't enter the year Excel will
assume it is the current year based on your computer clock, to be able to
enter 02/29 precede with apostrophe '02/29 (not visible in cell) or enter a
leap year
(02/29/04) and use custom format mm/dd
 
R

Ron Rosenfeld

I have the cells formated to show the dates as "2-Feb" for instance. But
when I type in 2/29, Excel converts it to "1-Feb". The only way I can bet it
to accept 2/29 is to format the cells as text. What am I doing wrong?
Click to expand...

Excel tries to parse your input as best it can. So it understands the 2 as
February. Since this is not a leap year, it next assumes that the '29'
represents a year, and assumes that you want the first day of February. Hence,
it is interpreting your input as 1 Feb 2029 (or 1929 depending on some other
settings) and displaying it as d-mmm which is how your cell is formatted.

Since there is no such date in this year, perhaps you should explain more
clearly what you are trying to accomplish.


--ron
 
M

Myrna Larson

Your "problem" is that if you type two digits that can be interpreted as a
month and day, then Excel assumes you mean that date in the CURRENT YEAR. So
what you type is equivalent to 2/29/2005. Since 2005 isn't a leap year, that's
not a valid date, so Excel assumes your 2 digits represent a month and year,
i.e. February, 1929, with the day of the month equal to 1, and formats the
cell as you see.

If you don't intend something the "looks like" a date to be interpreted as a
date, you must first format the cells as text (as you are doing), or precede
your entry with an apostrophe, i.e. type '2/29
 
Top