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

decimal	equivalent time (hh:mm)
0	00:00
0.25	06:00
0.5	12:00
0.75	18:00
1.0	24:00
1.25	30:00
1.5	36:00

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. Therefore, to subtract time in Excel:

Type the start time and the end time into your spreadsheet;
Sutract the start time from the end time;
Format the result as a time.

(The easiest way to format a worksheet cell to display a time is to select the cell and then select the 'Time' format from the drop-down menu in the Home tab of the Excel ribbon).

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

Format a cell as a time by selecting the 'Time' option from the drop-down menu on the ribbon

Excel Time Subtraction Examples

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

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

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

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.

Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page