Current Special! Complete Excel
for Excel 97 - Excel 2003, only
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
Create an Excel Calendar Control
Convert Excel Date Formats
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
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
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 firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates