Dates And Times In Excel 

Introduction
          

Almost all projects and applications in Excel use dates and times in some way.  A project may need to determine the number of days between two dates, or determine the date that is so many days in the future.  Timesheet projects need to determine the elapsed time between and start and end times, or determine how many hours are overtime. 

Before you can take advantage of some of the more power date and time formulas, you have to understand how Excel stores dates and times.  Regardless of how you have formatted a cell to display a date or time, Excel always internally stores dates And times the same  way.  

 


How Excel Stores Dates And Times

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day:   ddddd.tttttt . This is called a serial date, or serial date-time.

Dates

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0.  For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0.  The number 1 represents 1900-Jan-1.  It should be noted that the number 0 does not represent 1899-Dec-31.  It does not. If you use the MONTH function with the date 0, it will return January, not December.  Moreover, the YEAR function will return 1900, not 1899. 

Actually, this number is one greater than the actual number of days.  This is because Excel behaves as if the date 1900-Feb-29 existed.  It did not.  The year 1900 was not a leap year (the year 2000 is a leap year).  In Excel, the day after 1900-Feb-28 is 1900-Feb-29.  In reality, the day after 1900-Feb-28  was 1900-Mar-1 .  This is not a "bug".  Indeed, it is by design.  Excel works this way because it was truly a bug in Lotus 123.  When Excel was introduced, 123 has nearly the entire market for spreadsheet software.  Microsoft decided to continue Lotus' bug, in order to fully compatible.  Users who switched from 123 to Excel would not have to make any changes to their data.   As long as all your dates later than 1900-Mar-1, this should be of no concern.  

Times

The fractional portion of the number, ttttt, represents the fractional portion of a 24 hour day.  For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day.  Similarly, 6PM is stored at 0.75,  or 75% percent of a 24 hour day.  

As you can see, any date and time can be stored as the sum of the date and the time.  For example, 3PM on 19-Jan-2000 is stored internally as 36544.625.  When you enter a time without a value, such as entering 15:00 into a cell, the date portion is a zero.  The zero indicates that there is no date associated with the time.  You should remember that entering just a time does not automatically put in the current date.

 

 

The Two Date Systems Of Excel

In the section above, it was said that the date portion of a serial date-time represents the number of days since 1900-Jan-0.  This is true, but Excel can also treat the date portion of a serial date as the number of days since 1904-Jan-0.  This mode is, called the 1904-mode or 1904-system, is used for compatibility  with the Macintosh system.   The date mode applies to all dates within a workbook.  You cannot mix and match modes within a workbook.  Unless absolutely required, you should never use the 1904-system.  If you change systems after you've entered some dates in a workbook, these dates will appear to be off 4 years (because the serial dates haven't changed, only their display representation has).  Moreover, linking workbooks with different date systems will cause problems.  

 

        

Entering Two Digit Years In Excel

When you enter a date with only two digits in the year, you need to know how Excel treats the year.  Excel uses 29/30 as the "cutoff point" between 1900 and 2000.   For example, entering a year between 30 and 99 causes Excel to treat the year as 1930 - 1999.  Entering a year between 00 and 29 causes Excel to treat the year as 2000 - 2029.  Here are some examples:

1/1/00 = 2000-Jan-1
1/1/01 = 2001-Jan-1
1/1/29 = 2029-Jan-1
1/1/30 = 1930-Jan-1

1/1/31 = 1931-Jan-1
1/1/99 = 1999-Jan-1

The 29/30 cutoff is new to Excel97 and Excel2000.  Excel95 uses 19/20 as the cutoff year.  If you have any  doubts about how Excel will treat 2-digit years, use the full 4-digit year.  In Windows98 and Windows2000,  you can specify the cutoff year, from the Regional Setting control in the Windows Control Panel.  

Note that the DATE worksheet function does not follow these rules.  When you enter a number less than 1900 for the year parameter of the DATE  function,  DATE will add 1900 to that number. For example, =DATE(10,1,5)   returns the date 5-Jan-1910, because DATE  simply adds 10 to 1900 to compute the year.  Similarly, the formula  =DATE(150,1,5)   returns the date 2050-Jan-5, because DATE  adds 150 to the year 1900.   DATE does not follow the same "cut off" rules that cells do.  

 

 

Date And Time Arithmetic
          

Formulas for adding and subtracting dates and times can be found on the Date Arithmetic page. 

 

 

Worksheet Functions For Dates And Times

          

The Worksheet Functions For Dates And Times page contains many formulas related to dates and times. 

 

 

VBA Functions For Dates And Times

 

         The VBA Procedures For Dates And Times page contains a great deal of VBA code  related to dates and times. 

 

 

Holidays

          

You can find complete information about working with Holidays in Excel on the Holidays page. 

 

 

   

 

Topic Index For Dates And Times

        
Age, Calculating

Birthdays And Age


Date Intervals, Formulas For


Dates, Adding And Subtracting


Dates,  Differences Between
 

Dates, Distributing Across Months Or Years


Dates, Excel Serial Format


Dates, Finding With VBA .Find Method


Dates, Quick Entry


Dates, Two Digit Years


Day Of Week, Nth Day Of Week (VBA)

Day Of Week, Nth Day Of Week In A Month (Formula) 

Day Of Week, Returning Nth Day Of Week In A Year (Formula)

Days In Month, First And Last Days In Month

DATEDIF Function


Degrees, Minutes, And Seconds


Easter, Calculation Of Date


Holidays, Calculation Of Dates

Julian Dates

Last Weekday Of Month

Leap Year, Determining


Overtime Hours In Timesheets

Quarter, Determining From Date

Rounding Times

Thanksgiving, Calculation Of Date


Times, Adding And Subtracting


Times And Working Hours, Between Two Dates

Times, Quick Entry

Times, Rounding

Timesheets, Working With Regular And Overtime Hours


Time Zones

Week, First Monday Of

Week Numbers, Excel and ISO

Weekday, Counting Between Dates

Weekday, First And Last Of Month

Weekday, Nth Day Of Month (VBA)


Weekday, Nth Day Of Month (Formula)


Weekdays, Creating Series Of


Weeks, Difference Between Dates


Year, First Monday Of


Years, Entering Two Digit Years

 

 

 

    

 

Other Date And Time Related Procedures are described on the following pages.

        

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets


Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

 

 

See the Dates And Times Topic Index For Information