How to Subtract Time in Excel


Times in Excel

If you want to subtract time in Excel, it helps to understand the way that Excel stores times.

Times are stored as positive decimal values in Excel. It is only the formatting of an Excel cell that causes the numeric value to be displayed as a time, rather than a decimal.

The table below shows examples of decimals and the times that they represent in Excel.

Formatting Times in Excel

If you have a cell containing a positive decimal value, this can be diplayed as a time, by formatting the cell with a time format.

To do this:

  • Right click on the cell to be formatted
  • Select the option Format Cells...
  • Ensure the Number tab is selected in the window that pops up
  • Select the option Time from the list of Categories
  • Click OK

Further time formatting options can also be introduced if you select the Custom formatting category and enter your own format.

For example, the custom formatting style [h]:mm:ss will display more than 24 hours whereas the standard Time format will not.

Subtraction of Time in Excel

As times are stored as simple decimal values, you can subtract time in Excel in the same way that you can subtract any other numbers. This is shown in the following examples.


Excel Time Subtraction Examples

The following spreadsheet shows four examples of Excel time subtractions.

The formulas used are shown in column C of the top spreadsheet and the results are shown in the spreadsheet below.

 Formulas:
 ABC
1Start TimeEnd TimeTime Difference
205:3010:00=B2-A2
313:4517:02=B3-A3
400:00:1507:32:05=B4-A4
522:05:3623:01:19=B5-A5
 Results:
 ABC
1Start TimeEnd TimeTime Difference
205:3010:0004:30
313:4517:0203:17
400:00:1507:32:0507:31:50
522:05:3623:01:1900:55:43

Note that, in the results spreadsheet above, cells C2 and C3 are formatted with the time format hh:mm and cells C4 and C5 use the standard Excel Time format.