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 cell's contents 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.


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

Column C of the following spreadsheet shows four simple Excel time subtractions.

In each case, the time in column A is subtracted from the time in column B.

 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 time format hh:mm:ss.

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.

The easiest way to do this is to select the Time formatting from the drop-down format menu, which is generally located in the 'Number' group, on the Home tab of the Excel ribbon (see below):

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

If you want to use a different time format, select More Number Formats ... from the drop-down format menu to open up the 'Format Cells' dialog box. Within this dialog box, you can select one of several pre-programmed Time formats or define your own Custom format.

See the Excel Number Formatting page for more information on how to define custom time formats in Excel.