How to Subtract Time 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.
|
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:
|
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.
|
Formulas:
|
Results:
|
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.
