I'm rying to create a Month and Year (MM/YY) field on a spreadsheet to
document expiration date.
I've formatted the cell to show Month/Year (MM/YY, MM-YY, Mon-YY), but when
I just enter month and year, e.g. 03/06, it defaults to Mar-05.
Am I doing something wrong?
You are making the common error of assuming that cell formatting has anything
to do with data parsing with regard to input of dates.
Your format changes only affect how Excel displays the dates.
How Excel will parse your date INPUT depends on different rules. Briefly, if
you only input in the form of 00/00, it will interpret this (if possible) as
mm/dd and append the current year.
However, if "dd" is a number greater than the number of days in "mm"
interpreted as a month, then it will interpret the result as mm/yy.
The best solution depends on your application. One method would be to use a
form for input with different fields for Month and Year, and then have your
routines interpret that input according to your own rules.
Another method would be to enter the full date.
Still another method would be to preformat the cell as text, do your entry and
have an event VB Macro running to interpret the input according to your own
rules.
--ron
