Calculate 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 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:
 AB
1Start Time:03:45:30
2End Time:10:17:45
3Time Difference:=B2-B1
 Results:
 AB
1Start Time:03:45:30
2End Time:10:17:45
3Time Difference:06:32:15

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 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:

Method 1 - Format Cells Using the Ribbon Options

The easiest way to change cell formatting to 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 (see below):

Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected

Method 2 - Format Cells Using the 'Format Cells' Dialog Box

This method can be used in earlier versions of Excel that do not have the ribbon:

  • Right click on the cell(s) to be formatted;
  • Select the option Format Cells... to display the 'Format Cells' dialog box;
  • Ensure the Number tab of the dialog box is selected;
  • From the Category: list, select the option Time and click OK.

Example 2

The following spreadsheet shows a simple subtraction of two dates and times.

 Formulas:
 AB
1Start Date & Time:19-Jan-2011   21:00
2End Date & Time:21-Jan-2011   15:00
3Time Difference:=B2-B1
 Results:
 AB
1Start Date & Time:19-Jan-2011   21:00
2End Date & Time:21-Jan-2011   15:00
3Time Difference:42:00

Setting Cells to Have the [h]:mm Format:

  • Right click on the cell(s) to be formatted;
  • Select the option Format Cells... to display the 'Format Cells' dialog box;
  • Ensure the Number tab of the dialog box is selected;
  • From the Category: list, select the option Custom and type [h]:mm into the box on the right;
  • Click OK.

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.


Common Time Difference Error

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).

Excel Cell Displaying #####
cell containing a negative value with date or time formatting

You can display the underlying numerical value by formatting the cell with the 'General' formatting type.