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 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.
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.
When you calculate a time difference in Excel, you probably want the result to be displayed as a time.
If it is not, you can format the cell to have a 'Time' format, using any of the following methods:
This method can be used in earlier versions of Excel that do not have the ribbon:
The following spreadsheet shows a simple subtraction of two dates and times.
Setting Cells to Have the [h]:mm Format:
In the example above, 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 representation for 42 hours 0 minutes.
Note that cell B3 of the above results spreadsheet has the cell formatting [h]:mm.
The square brackets in the [h]:mm format definition tell Excel to display the total number of hours, even if this is greater than 24. If the square brackets were not included, Excel would break down the result into a date plus the remaining number of hours.
Excel does not display negative times. Therefore if the result of your subtraction formula is a negative value, and the cell containing the formula has date or time formatting, the result will be displayed as a row of hashes (see below).
You can display the underlying numerical value by formatting the cell with the 'General' formatting type.