Excel Dates and Excel Times

When using dates and times in Excel it is of great benefit, if you first understand Excels way of using dates and times.

Excel (by default) uses the 1900 date system. This simply means that the date 1 Jan 1900 has a true numeric value of 1, 2 Jan 1900 has a value of 2 etc. These values are called "serial values" in Excel and it is these serial values that allows us to use dates in calculations.

Times are very similar BUT Excels sees Times as decimal fractions, with 1 being the time 24:00 or 00:00. 18:00 has true value of 0.75 because it is three quarters of 24 hours, or the whole number 1.

To see the true value of a date and/or time simply format the cell as "General". For example the date and time 3/July/2002 3:00:00 PM has a true value of 37440.625 with the number after the decimal representing the time and the 37440 being the serial value for 3/July/2002

For more detail see: "How Microsoft Excel stores dates and times" in the help.

A Date Bug?

Excel incorrectly assumes that the year 1900 was a leap year. What this means is Excels internal date system believes there was a 29 Feb 1900, when they wasn't! The surprising part is, Microsoft did this intentionally! Read the text below taken from the Microsoft knowledge base:

When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.

When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This 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 worksheets from one program to the other.

Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.

If this behavior were to be corrected many problems would arise, including the following:

Almost All Dates In Current Microsoft Excel Worksheets And Other Documents Would Be Decreased By One Day. Correcting This Shift Could Take Considerable Time And Effort, Especially In Formulas That Use Dates.
Some Functions, Such As The WEEKDAY Function, Would Return Different Values; This Might Cause Formulas In Worksheets To Work Incorrectly.
Correcting This Behavior Would Break Serial Date Compatibility Between Microsoft Excel And Other Programs That Use Dates.

If the behaviour remains uncorrected, only one problem occurs:

The WEEKDAY Function Returns Incorrect Values For Dates Before March 1, 1900. Because Most Users Do Not Use Dates Before March 1, 1900, This Problem Is Rare.

For more information about this issue, please see the following article in the Microsoft Knowledge Base:

Q106339 : XL: Days Of The Week Before March 1, 1900, Are Incorrect

NOTE: Microsoft Excel correctly handles all other leap years, including century years that are not leap years (for example, 2100). Only the year 1900 is incorrectly handled.

For more information, please see the following article in the Microsoft Knowledge Base:

Q118923 : XL: Method To Determine Whether A Year Is A Leap Year