How to Subtract Time in Excel -10 Quick Methods

Method 1. Subtracting Time between Two Cells to Get the Elapsed Time

To calculate the working hours of the employees in an office, you may need to calculate the time difference between cells. The following dataset contains the ‘Entry Time’ & ‘Exit Time’.

1.1 With a Simple Formula

STEPS:

Select E5.
Enter the formula:

=D5-C5

Press Enter.

The formula subtracts the values in D5 & C5 and returns a value in the same format in E5.

Time format needs to be changed:

Go to the Home tab and select Number to open the ‘Format Cells’ window.

Select a Time Format and click OK.

Hours, minutes and seconds will be displayed in the Working Hour column.

Drag down the Fill Handle to see the result in the rest of the cells.

1.2 With the IF Function

The IF Function tests the logic.

STEPS:

Select C5.
Enter the formula:

=IF(D5>=C5, D5-C5, D5+1-C5)

Press Enter to see the result.

The IF Function checks if the value of D5 is greater or equal to C5. If it is true, it will return the subtraction of the values. If it is false, it will add 1 to D5 and subtract it from C5.

To change the format, go to ‘Format Cells’ and select a Time format.

Click OK.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

1.3 With the MOD Function

The MOD Function returns the remainder after a number is divided.

STEPS:

Select E5.
Enter the formula:

=MOD((D5-C5),1)

Press Enter to see the result.

The MOD Function will subtract the values of D5 from C5. The subtracted value will be divided by 1.

Change the Time Format.

Drag down the Fill Handle to see the result in the rest of the cells.

1.4 With the TEXT Function

The TEXT Function converts numbers to the text.

STEPS:

Select E5.
Enter the formula:

=TEXT(D5-C5, “h:mm:ss”)

Press Enter to see the result.

The TEXT Function stores the subtraction of D5 and C5 in the first argument and returns the string in hours, minutes and seconds format.

If you want to see only hours and minutes, enter the formula:

=TEXT(D5-C5,“h:mm”)

Press Enter to see the result.

If you want to see only hours, enter the formula:

=TEXT(D5-C5,“h”)

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Subtracting Time with the TIME Function in Excel

The TIME Function stores hours in the first argument, minutes in the second argument, and seconds in the third argument.

STEPS:

Select D5.
Enter the formula:

=C5-TIME(1,30,0)

Press Enter to see the result.

Here, lunch hour is 1 hour and 30 minutes. The TIME function contains 1 in the first argument and 30 in the second argument. It contains 0 in the third argument.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 3 – Calculating and Displaying a Negative Time Difference in Excel

Excel doesn’t display negative time values by default.

Observe the dataset below.

STEPS:

Go to the File tab and select Options.

In Excel Options, select Advanced.
Check ‘Use 1904 date system’.

Click OK to see the result.

Alternatively, use the formula below in E5.

=IF(D5-C5>0, D5-C5, “-” & TEXT(ABS(D5-C5),”h:mm”))

Press Enter and drag down the Fill Handle to see the result in the rest of the cells.

The IF function will display the subtraction between D5 and C5 if the subtraction is greater than 0. Otherwise, it will display a minus sign along with the absolute value of the subtraction.