How to Subtract Time in Excel

Times in Excel

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

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

The table below shows examples of numerical values 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 format a cell with a time format:

  • 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 illustrated in the examples below.


Excel Time Subtraction Examples

The following spreadsheet shows four examples in which Excel times are subtracted.

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

 Formulas:
  A B C
1 Start Time End Time Time Difference
2 05:30 10:00 =B2-A2
3 13:45 17:02 =B3-A3
4 00:00:15 07:32:05 =B4-A4
5 22:05:36 23:01:19 =B5-A5
 Results:
  A B C
1 Start Time End Time Time Difference
2 05:30 10:00 04:30
3 13:45 17:02 03:17
4 00:00:15 07:32:05 07:31:50
5 22:05:36 23:01:19 00:55:43

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


Return to the Basic Excel Page

Return to the ExcelFunctions.net Home Page