Excel Dates and Times


Most users have worked with Excel dates and times, to some extent. However, not all users fully understand the way that dates and times are stored within Excel, and this prevents them from getting the most out of their Excel functions and formulas.

Excel dates and times are actually stored as simple numbers within Excel. It is only the Formatting of a spreadsheet's cells, that causes a number to be displayed as a date, time, or date & time.

Within Excel, dates and times are coded as follows:

Dates Stored as positive integers
For example, on the windows operating system:
1 = 1st January 1900
2 = 2nd January 1900
40209 = 31st January 2010
Times Stored as decimals, between 0 and 1, which represent a proportion of the day
For example:
0 = 00:00 hrs
0.25 = 06:00 hrs
0.5 = 12:00 noon
Dates & Times Stored as decimals, comprised of
- an integer (representing the date), and
- a fraction between 0 and 1 (representing the time)
For example, on the windows operating system:
1.5 = 12:00 hrs on 1st January 1900
40208.25 = 06:00 hrs on 30th January 2010
40209.5 = 12:00 hrs on 31st January 2010


Adding and Subtracting Dates and Times in Excel

The storage of dates & times as numbers is useful, as it enables the addition, subtraction and comparison of dates and times in Excel. This is shown in the following examples, which use the date and time values in the example spreadsheet below:

  A B
1 20/08/2010 31/08/2010
2 13:00 17:15
3 30/08/2010 17:00 31/08/2010 09:00
4 30/08/2010 17:00 23:00

Example 1

=B1-A1
- Calculates the number of days between 20/08/2010 & 31/08/2010 (in cells A1 and B1)
- This gives the result 11

Example 2

=B2-A2

- Calculates the number of hours and minutes between 13:00 & 17:15 (in cells A2 and B2)
- This gives the result 04:15

Example 3

=B3-A3

- Calculates the number of hours and minutes between 30/08/2010 17:00 & 31/08/2010 09:00 (in cells A3 and B3)
- This gives the result 16:00

Example 4

=A4+B4

- Calculates the time that is 23 hours after 17:00 on 30/08/2010
- This gives the result 31/08/2010 16:00

Common Problem

Formatting Dates and Times in Excel

The results in of the above Excel date and time formulas will generally NOT have the correct formatting when first input into your spreadsheet. This will give the initial impression that the formula has not worked - however, this is not the case.

The cells can be made to show the correct result by formatting into a 'General', 'Date', 'Time', or 'Date & Time' format.

To do this :

  • Highlight the cell(s) with the wrong formatting
  • Right click with the mouse
  • Select the Format Cells ... option and ensure the Number tab is selected
  • Under the Category heading, select the required formatting style and click OK

Click here for more details on Excel formatting
Return to the Basic Excel Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net