OzGrid

Excel Dates and Excel Times

< Back to Search results

 Category: [Excel]  Demo Available 

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

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

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

 

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Due to the coronavirus pandemic, opening times can change. Please check POST OFFICE OPENING TIMES NEAR ME on webiste open4u.co.uk

Gallery



stars (0 Reviews)