Forum Discussion
Issue with the s/n for 2/29/1900 when using EOM function
When using the EOM function for February 1900, Excel is using a serial number for 2/29/1900 of 59 not 60. I am aware the 1900 was not a leap year, but I understand that for compatibility Microsoft coded 2/29/1900 as a legitimate date in Excel for compatiblity (I believe) with Lotus 123. This creates a problem in a annual calendare I recently created that has a potential years from 1900 - 9999. But because of the issue with 2/29/1900 Excel will not properly apply the conditional formatting for the year 1900.
Below is an example of calendar when the year 1900 is chosen and one when any subsequent year is chosen.
All you can do is cater for the wrong Feb 29 1900 date in your conditional format I'm afraid.
Or set your Excel file to use the 1904 date system. But since that isn't the default AND copying and pasting into other files (which are very likely set to the default 1900 date system) will cause your pasted dates to be off by 4 years, I do not recommend to do this.
You'd have to add a test in the conditional formatting rules to check if the year is 1900, and adjust them accordingly.
Moreover, all days in Jan-Feb 1900 are to be shifted to the right excluding Feb 29 to make calendar correct. Jan 01, 1900 is Monday, not Sunday. But that all depends which formulae do you use behind.
What I find odd: If you pull up a calendar template and put the year at 1900, it gives you a Feb 29, 1900. But, if you put in a formula: =EOMONTH(DATE(1900,2,1),0),
It returns 2/28/00.
EOMONTH() checks in which month the date is according to Excel calendar, after that internally calculates end of month correctly taking into account leap years. Thus =EOMONTH("1900-02-28",0) and =EOMONTH("1900-02-29",0) both return Feb 28, 1900.
As for the template it depends on how it is done. For example, this template from Excel collection, if slightly modify, correctly returns first two months of 1900
