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:
|
|||||||||
| Times |
Stored as decimals, between 0 and 1, which represent a proportion of the day
For example:
|
|||||||||
| 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:
|
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
- This gives the result 11
Example 2
- This gives the result 04:15
Example 3
- This gives the result 16:00
Example 4
- This gives the result 31/08/2010 16:00
Common Error
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
