After formatting Excell cells to read dates as MAR-01, I type the following:
12-05 gives me Dec-06
11/02 gives me Nov-06
but
12/57 gives me Dec-57
has happened on two PCs.
??
jesjes
You need to understand how Excel parses date inputs. And you also need to
realize that how you format a cell does NOT affect how Excel interprets your
date input.
Excel interprets your input based on the date format of your OS
(Windows--Control Panel Regional Settings)
Here in the US, my short date format is mm/dd/yy
If I input something like 12-05, since twelve is a valid month, and 5 is a
valid day, Excel will interpret this as 5 December and append the current year
-- 2006.
The format you selected (MAR-01) is a month - year format, so it displays the
Dec and the 06 which is the current year. But if you look in your formula bar,
you will see that the interpreted date is 12/05/2006.
The same thing happens with 11/02 --> 11/02/2006 and displayed as Nov-06
However, when Excel sees 12/57, 57 is not a legitimate day of a month,
therefore Excel's rules say to interpret the 57 as a year, and assume that the
day of the month is '1'. The date in the formula bar will be 12/01/1957 and
the format of mmm-yy will show Dec-57.
Whether two digit date not between 1-31 is interpreted as 19xx or 20xx is also
dependent on a setting external to Excel in the Regional and Language OS
settings.
One solution is to be unambiguous in your entry of years, especially if the
year will be within the 1-31 range.
12-2006
11-2006
12-1957 or 12-57
--ron
