Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Dates and Excel Times

| | Information Helpful? Why Not Donate.

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel Date & Times Index. Got any Excel Questions? Free Excel Help Working with Excel Dates and Times

ALSO SEE: Create an Excel Calendar Control | Convert Excel Date Formats

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

Some other good links on Dates and Times

Back to: Excel Date & Times Index. Got any Excel Questions? Free Excel Help

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft