In short, the Excel DateTime era does not coincide with the VBA DateTime era. Although, they are the same as soon as you get February 28, 1900.
From the Joel Spolksy Blog :
In most modern software environments, dates are stored as real numbers. The integer part of a number is the number of days since then some agreed date in the past, called an era. In Excel today, the date, June 16, 2006, is stored as 38884, counting the days when January 1, 1900 - 1.
I started working through the various date and time functions in Basic and the date and time functions in Excel, trying to figure out when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as an era instead of January 1, 1900, but by for some reason, today the date was the same in Excel as in Basic.
A?
I went looking for an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.
"Oh," he told me. " Departure February 28, 1900. "
“It's 59,” I said.
"Now try March 1st."
"This is 61!"
"What happened to 60?" Ed asked.
"February 29, 1900 was a leap year! It is divided into 4!
“Good guess, but no cigar,” Ed said, and left me at a loss at that time.
Unfortunately. I did some research. Years that are divisible by 100 are not leap years unless they are also divisible by 400.
1900 was not a leap year.
“This is a mistake in Excel!” I exclaimed.
“Well, not really,” Ed said. “We had to do it like this because we need to be able to import Lotus 123 worksheets.”
"So, is this a bug in Lotus 123?"
"Yes, but probably intentional. The lotus should have fit in 640K. It's not that much memory. If you ignore 1900, you can figure out if a given year is a leap year, just looking to see, two bits are zero. It's very fast and simple. Lotus probably thought it didn't matter to be mistaken for those two months in the past. It seems like the base guys wanted to be anal for these two months, so they brought the era back one day. "
"Aargh!" I said, and went away to study why in the options dialog called 1904 Date System.
The information below was taken from this Superuser response .
As described in Microsoft KB 214058 :
ADDITIONAL INFORMATION
When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with the date systems used by other spreadsheet programs.
However, in this date system, the year 1900 is misinterpreted as a leap year. Since February 19, 1900 (“leap day”), the day of the week for any date until March 1, 1900 (the day after the “leap day”) is not calculated correctly.
"Other spreadsheet programs" refer to Lotus 1-2-3 , which was quite popular at the time and was misplaced this year. 1900 was a leap year. This is explained in more detail in KB 214326 :
ADDITIONAL INFORMATION
When Lotus 1-2-3 was first released, the program assumed that 1900 was a leap year, although in fact it was not a leap year. This helped the program cope with the lethal years and did not harm almost all date calculations in Lotus 1-2-3.
When Microsoft Multiplan and Microsoft Excel were released, they also suggested that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move sheets from one program to another.
Although it is technically possible to correct this behavior, so current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages outweigh the benefits.
If this behavior needs to be fixed, there will be many problems, including the following:
- Almost all dates in current Microsoft Excel worksheets and other documents will be reduced by one day. Correcting this shift will require considerable effort and time, especially in formulas that use dates.
- Some functions, such as the WEEKDAY function, will return different values; this can lead to malfunctioning formulas in worksheets.
- Correcting this behavior will violate date and time compatibility between Microsoft Excel and other programs that use dates.
If the behavior remains incorrect, there is only one problem:
- The WEEKDAY function returns incorrect values for dates before March 1, 1900. Since most users do not use dates until March 1, 1900, this problem is rare.