Calculate Time Difference in Excel
How to Calculate a Time Difference in Excel
The easiest way to calculate a time difference in Excel is to simply subtract one time from the other.
This works because Excel stores dates as integers and times as decimal values (see the page on Excel dates for more details). It is only the formatting of an Excel cell that causes the cell's contents to be displayed as a date or time, rather than as a simple number.
Therefore, when you want to calculate the difference between two times in Excel, you can simply subtract the time values in the same way as you would subtract any other numeric values.
Date and Time Difference Examples
Example 1
Cell B3 of the following spreadsheet shows a simple example of how to calculate a time difference by subtracting the time in cell B1 from the time in cell B2.
|
Formulas:
|
Results:
|
In the example above, the time in cell B1 is internally represented by the number, 0.156597222 and the time in cell B2 is internally represented by the number 0.428993056. Subtracting these two numbers gives the result 0.272395833, which, when formatted as a time, is 06:32:15 (i.e. 6 hours 32 minutes and 15 seconds).
Formatting Cells as Times
When you calculate a time difference in Excel, you may want the result to be displayed as a time.
If it is not already displayed as a time, you can format the cell to have a 'Time' format, using any of the following methods:
Method 1 - Format Cells Using the Excel Ribbon Options
The easiest way to apply a simple 'Time' format is to select the cell(s) to be formatted and then select the Time option from the drop-down menu in the ribbon. This is found in the 'Number' group on the Home tab of the ribbon (see below):
Method 2 - Format Cells Using the 'Format Cells' Dialog Box
If you require a greater selection of time formats, you may prefer to apply cell formatting using the 'Format Cells' dialog box as follows:
- Select the cell(s) to be formatted.
-
Open up the 'Format Cells' dialog box by either:
- Clicking on the dialog box launcher in the 'Number' grouping, on the Home tab of the Excel ribbon (see rightabove).
- Using the keyboard shortcut Ctrl + 1 (i.e. press the Ctrl key and while holding this down press 1).
- Ensure the Number tab of the 'Format Cells' dialog box is selected.
-
From the Category: list, select the option Time.
(If you want a time other than the default, you can then select one of the options from the list on the right hand side of the 'Format Cells' dialog box.) - Click OK to apply the selected format and close the 'Format Cells' dialog box.
Example 2
The following spreadsheet shows a simple subtraction of two dates and times.
|
Formulas:
|
Results (with formatting):
|
In this example, the date and time in cell B1 is internally represented by the number, 40562.875 and the date and time in cell B2 is internally represented by the number 40564.625. Subtracting these two numbers gives the result 1.75 which is Excel's internal value for the time 42 hours 0 minutes.
Formatting Cells to Display Times With More Than 24 Hours:
It is likely that the standard time format (applied when you use the ribbon option to format a time) will only display times up to 24 hours.
However, further time formatting options are available in the 'Format Cells' dialog box. To apply a time format that displays more than 24 hours:
- Open up the 'Format Cells' dialog box (as described above).
- Ensure the Number tab of the dialog box is selected.
- From the Category: list, select the option Time.
- From the 'Type' list that appears on the right hand side of the 'Format Cells' dialog box, select a time format that displays an hour value that is greater than 24.
- Click OK to apply the selected format and close the dialog box.
Common Time Difference Errors
If Excel has a problem displaying the contents of a cell in a time or a date format, it will, instead display a row of hashes (see below).
cell that is unable to display values in date or time format
Possible reasons for this are:
Possible Reason 1
The cell may not be wide enough to display the time or date in the specified format.
Solution
increase column width by dragging the separator bar right
You can alter the width of an Excel column by dragging the bar (shown in the above image on the right) until the cell is wide enough for the contents.
Alternatively, if you double-click on this bar, the cell should automatically re-size to fit the contents.
Possible Reason 2
Excel cannot display negative times. Therefore if the result of your subtraction formula is a negative value, and the cell containing this formula has date or time formatting, the result will be displayed as a row of hashes.
Solution
You can display the underlying numeric value by formatting the cell with the 'General' formatting type.