Microsoft Excel day number is the number of days that have elapsed since the initial epoch defined as midnight preceeding December 31, 1899 in the Gregorian calendar.
Microsoft Excel uses the Julian calendar leap year rule for 1900 (hence with leap day February 29, 1900) and the Gregorian calendar for the years 1901 - 9999; thus for dates from 1 March 1900 a time is stored as the number of days in the Gregorian calendar not from December 31, but from December 30, 1899, 00:00; optionally Microsoft Excel can also use the Apple Macintosh epoch, which avoids the complication by starting later; it represents times by a number that is 1462 less.
The fictional (or Julian) leap day February 29, 1900 in Microsoft Excel was introduced intentionally in order to maintain compatibility with then market leader Lotus 1-2-3. Designers of Lotus 1-2-3 had probably chosen this simplified behaviour in order to save some precious processing time and program space. For the rest of its time range 1900 - 9999 Excel uses the Gregorian calendar, hence e.g. there is no February 29, 2100.
Microsoft Excel (and Lotus 1-2-3) technically considers the epoch of December 31, 1899 as January 0, 1900 or a serial date of zero (consequently, December 31, 1899 cannot be used). January 0, 1900 can be processed and formatted in Excel Worksheets, just as any other date.
The GNU free alternative to Microsoft Excel, OpenOffice.org Calc uses similar day numbering system, but it does not have the Julian leap day in 1900. Thus its epoch is fixed on December 30, 1899. OpenOffice.org Calc is able to handle negative day numbers, and can work with dates between 32767 BCE and 32767 CE. The dates prior to October 15, 1582 are treated as in Julian Calendar.
In terms of the Julian day number the Microsoft Excel day number can be defined as:
- MED = JD – 2415018.5
This formula is correct for the Time Zone 0 for the days after March 1st, 1900.