Excel Date Quandary...
Posted by mccdaddy on January 09, 2002 6:47 PM
When I type 11/01 (cell formatted as mm/yy) in a cell, it appears as 11/02. The only way I can get 11/01 to show is to type 11/01/01 or 11/2001. I've used this spreadsheet for years and this is the first time I'm encountered this quirk. Is there any way to make Excel ignore the day and only give me the correct mm/yy?
Posted by Jacob on January 09, 2002 7:47 PM
Hi
Excel is just reading 11/01 as 11 month and 01 day then formatting it as mm/yy since it doesnt know the year it assumes this year 2002. You will have to type the full date mm/dd/yy or mm/yyyy to get what you want. Since you only supply part of the date in this case excel is guessing wrong as to what you meant, not really a quirk.
HTH
Jacob
Posted by George J on January 10, 2002 8:29 AM
Try going into cell format and selecting custom type. You should be able to add your own variations of the data in there if it has been removed.
George
Posted by mccdaddy on January 10, 2002 9:13 AM
If Excel is assuming mm/dd, even though I have the cell custom formatted as mm/yy, then why do I have no problem with 11/99, or 11/00. Excel can't be assuming 99 and 00 are days?
Posted by Jacob on January 10, 2002 11:56 AM
Hi
The day field can be 1-31 right and the month can be 1-12 if you put 11/99 it knows that the 99 has to be a year and will assume the other is the month. regardless of the format since the date is really just a number 35621 for example and that number is formatted the way you want. If you type two numbers 5/6 and they could be day/month then that is what excel will assume.
HTH
Jacob
