How to Subtract Time in Excel

Related Page:
Subtract Dates In Excel

Before learning how to subtract time in Excel, it is important to first 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.

Therefore you can subtract time in Excel, in the same way that you can subtract any other numbers.

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

decimal equivalent time
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

If you have a cell containing any positive decimal value, this can be diplayed as a time, by formatting the cell with the time format [hh]:mm or [hh]:mm:ss (depending on whether you want to show hours, minutes and seconds or just hours and minutes).

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 Custom from the list of Categories and type the
format style ([hh]:mm or [hh]:mm:ss) into the box on the right
- Click OK

Note that the square brackets surrounding the hour part of the time format definition (i.e. [hh]) 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.

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 spreadsheet on the left and the results are shown in the spreadsheet on the right.

  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
  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 are formatted with the format [hh]:mm:ss.

Return to the Basic Excel Page

Return to the Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013