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:
If the behaviour remains uncorrected, only one problem occurs:
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
|Convert Dates To Excel Formatted Dates|
|Convert Excel Formula/Functions to Values|
|Converting Numbers Seen Text By Excel to Real Numbers|
|Convert Excel Dates/Time to True Dates & Times|