Excel Dates and Times
Understanding Excel Dates and Times
If you want to work with Excel dates and times, it is useful to understand the way that Excel stores dates and times.
Dates and times are actually stored as simple numbers within Excel. It is only the Formatting of a spreadsheet's cells, that causes its contents to be displayed as a date, time, or date & time.
The following table shows how dates and times are coded within Excel:
| Dates |
Dates in Excel are stored as positive integers. For example, on most computer systems:
Therefore, when entered into a spreadsheet, the integer values, 1, 2 and 42736 are displayed as follows (depending on the cell formatting):
|
|||||||||||||||||||||||||||
| Times |
Times in Excel are stored as decimals, between 0 and 1, which represent a proportion of the day. For example:
Therefore, when entered into a spreadsheet, the decimal values, 0, 0.25 and 0.5 are displayed as follows (depending on the cell formatting):
|
|||||||||||||||||||||||||||
| Dates & Times |
Date & Time values in Excel are stored as decimals, comprised of:
For example, on most computer systems:
Therefore, when entered into a spreadsheet, the above values are displayed as follows (depending on the cell formatting):
|
Adding and Subtracting Dates and Times in Excel
Because Excel stores dates & times as numbers you can add, subtract and compare dates and times in Excel in the same way that you would add or subtract any other numbers.
Examples are provided below:
Example 1 - Calculate the Number of Days Between Two Dates
| A | B | |
|---|---|---|
| 1 | 08/20/2017 | =A2-A1 |
| 2 | 08/31/2017 |
Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of days between the dates 08/20/2017 & 08/31/2017 (stored in cells A1 and A2).
This gives the result 11.Example 2 - Calculate the Difference Between Two Times
| A | B | |
|---|---|---|
| 1 | 13:00 | =A2-A1 |
| 2 | 17:15 |
Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of hours and minutes between the times 13:00 & 17:15 (stored in cells A1 and A2).
This gives the result 04:15.
(Note that, in order to display this result, cell B1 should be formatted to have the time format "hh:mm").
Example 3 - Calculate the Difference Between Two Dates and Times
| A | B | |
|---|---|---|
| 1 | 08/30/2017 17:00 | =A2-A1 |
| 2 | 08/31/2017 09:00 |
Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of hours and minutes between the two dates & times stored in cells A1 and A2.
This gives the result 16:00.
(Note that, in order to display this result, cell B1 should be formatted to have the time format "hh:mm").
Example 4 - Add a Time to a Date and Time
| A | B | |
|---|---|---|
| 1 | 08/30/2017 17:00 | =A1+A2 |
| 2 | 23:00 |
Cell B1 of the above spreadsheet on the right uses the formula =A1+A2 to calculate the date and time that is 23 hours after the date & time 08/30/2017 17:00.
This gives the result 08/31/2016 16:00.
(Note that, in order to display this result, cell B1 should be formatted to have the date and time format "mm/dd/yyyy hh:mm").
Common Problem
Formatting Dates and Times in Excel
The results in of the above Excel date and time formulas may not have the required formatting when they are first entered into your spreadsheet. This may 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, depending on the required result type.
The easiest way to change the formatting of one or more Excel cells is to select the cell(s) to be formatted and then select the required formatting type from the drop-down menu in the ribbon. This is generally found in the 'Number' group on the Home tab (see below):
If you don't see the formatting style that you require, select the option More Number Formats ... from the drop-down format menu. This opens up the 'Format Cells' dialog box, which provides several additional pre-programmed formats or allows you to define your own Custom format.