How to Subtract Time in Excel (10 Quick Methods)

There are many times we need to subtract time in our Excel worksheet. We often subtract time to find elapsed time. In this article, we will see various methods to subtract time easily in an Excel worksheet. When you are working with a large dataset in Excel, these techniques will save a lot of time.

How to Subtract Time in Excel: 10 Ways

1. Subtract Time between Two Cells to Get the Elapsed Time

Frequently, we need to calculate the time difference between cells to get the elapsed time. We require this operation to calculate the working hours of the employees in an office. There are various ways to subtract time between two cells. We will discuss these techniques below. To make the explanation easier, we will use a dataset that contains the ‘Entry Time’ & ‘Exit Time’ of some employees.

1.1 With Simple Formula

In this sub-method, we will use a simple formula to calculate the time difference between two cells.

Follow the steps below.

STEPS:

• Firstly, select Cell E5.
• Secondly, type the formula:

• Now, hit Enter.

Here, the formula is subtracting the values of Cell D5 & Cell C5. Then, give the result in the same format in Cell E5.

We need to change the time format to display the working hours properly.

• For that purpose, go to the Home tab and select the Number dialog icon like below. The ‘Format Cells’ window will occur.

• After that, select the Type of Time Format you want to display and click OK.

• Then, you will see hours, minutes & seconds in the Working Hour column.

• Finally, use the Fill Handle to see results in all cells.

1.2 With IF Function

We can also calculate the time difference between two cells using the IF Function. The IF function tests the logic and returns a value if it is true. Otherwise, it returns another value.

Pay attention to the steps below for this technique.

STEPS:

• In the first place, select Cell E5.
• Now, type the formula:

• Next, press Enter to see the result.

Here, the IF Function will first check if the value of D5 is greater or equal to C5. If it is true, then it will subtract them and will show in the output. If it is false, then, it will add 1 with D5 and then subtract from C5.

• To change the format, go to ‘Format Cells’ & select your Type.

• After clicking OK, you will see hours and minutes like below.

• Finally, drag down the Fill Handle to see results in all cells.

1.3 With MOD Function

We can use the MOD Function for the same purpose. The MOD Function generally returns the remainder after a number is divided by a divisor. The number is the first argument and the second argument is the divisor.

Follow the steps below.

STEPS:

• In the beginning, select Cell E5.
• Now, type the formula:

• Next, press Enter to see the result.

Here, the MOD Function will subtract the values of cell D5 from cell C5. Then, the subtracted value will be divided by 1.

• Again, change the Time Format.

• Finally, use the Fill Handle to see results in the Working Hour column.

1.4 With TEXT Function

The TEXT Function can also calculate the time difference. Generally, the TEXT Function converts numbers to the text within a worksheet. Initially, the function converts any numeric value into a text string.

Let’s follow the steps to find out how it works.

STEPS:

• Firstly, select Cell E5 and type the formula:

• Hit Enter to see the result.

In this case, the TEXT Function will store the subtraction of Cell D5 and Cell C5 in the first argument and then express the string in hours, minutes, & seconds format.

• Similarly, if you want to display only hours and minutes, then, type the formula:

• Press Enter to see the result.

• Again, if you want to display only hours, then, type the formula:

• Now, press Enter to see the result.

• In the end, use the Fill Handle to see results in all cells.

2. Subtract Time with TIME Function in Excel

There are some times when we need to subtract a specific amount of hours from a time period. In those cases, the TIME Function is very effective. The TIME function stores hours in the first argument, minutes in the second argument, and seconds in the third argument.

We will use a dataset that contains the working hours of some employees. We will subtract the lunch hour from it.

Observe the steps to know more about this method.

STEPS:

• Select cell D5 at first.
• Now, put the formula:

• Now, press Enter to see the result.

Here, our lunch hour is 1 hour and 30 minutes. So, the TIME function contains 1 in the first argument and 30 in the second argument. It contains 0 in the third argument because we don’t have any seconds in our lunch hour time.

• Lastly, use the Fill Handle to see results in all cells.

3. Calculate and Display Negative Time Difference in Excel

Sometimes, when we subtract time, the subtraction can be negative. Excel doesn’t display negative time values by default.

You can see this problem in the picture below.

To fix this problem, follow the steps below.

STEPS:

• Firstly, go to the File tab and select Options.

• Secondly, select Advanced from the Excel Options.
• Then, check the ‘Use 1904 date system’.

• Click OK to see results like below.

• Alternatively, you can use the below formula in Cell E5.

• Hit Enter and use the Fill Handle to see the result.

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

4. Subtract Time and Display in a Single Unit in Excel

In this method, we will find the difference between two times using a simple formula and convert the difference into a decimal number.

We will use the same dataset here.

STEPS:

• Select Cell E5 and type the formula:

• Hit Enter to see the result.

• Use the Fill Handle to see results in other cells.

• To display only the integer value, we are going to use the INT Function and type:

• Press Enter to see the result.

• Again, drag the Fill Handle down to see results in all cells.

• To convert into minutes, multiply the formula by 1440.

• Press Enter and use the Fill Handle to see the results like below.

• To convert into minutes, multiply the formula by 86400.

• Press Enter and use the Fill Handle to see the results like below.

5. Calculate Time Difference in a Unit Ignoring Other Units

We can also calculate the time difference in a unit ignoring other units. We can use this technique to display hours, minutes, and seconds in different.

Let’s observe the steps below.

STEPS:

• Select a cell and type the formula:

• Hit Enter and drag down the Fill Handle to see the result.

Here, the HOUR Function will subtract the values of Cell D5 and C5 and only display the hour part.

• To show only the minute part at the output, use the MINUTE Function and type the formula:

• Again, press Enter and use the Fill Handle to see the result.

• Use the SECOND Function to show only second part at the output. Type the formula:

• After that, press Enter and drag down the Fill Handle to see the result.

• Finally, we display the values separately like below.

Read More: How to Calculate Time Difference in Minutes in Excel?

6. Use NOW Function to Subtract Time from Current Time

We can use use the NOW Function when we need to subtract time from the current time.

Follow the steps below.

STEPS:

• Select Cell D5 at first and type the formula:

Here, the formula will subtract the value of Cell C5 from the current time.

• Then, press Enter and use the AutoFill option to see the results.

7. Subtract and Display Time in Hours, Minutes & Seconds Unit

Sometimes we need to display the subtracted value with some texts. In this method, we will display the subtracted values with hours, minutes & seconds units. We will again use the same dataset here.

Let’s follow the steps below.

STEPS:

• First, select Cell E5 and type the formula:

• Now, hit Enter.

• Then, go to the Number Format dialog icon in the Home tab and select it.

• Select ‘Custom’ from the Format Cells window.
• Put the text in the Type field:

• Click OK to see results like below.

• Finally, use the Fill Handle to see results in all cells.

Read More: How to Subtract and Display Negative Time in Excel?

8. Subtract Hours in Excel

We can subtract the required hours from a given time using the TIME function or basic arithmetic operators.

8.1 Subtract Time Under 24 Hours

In cell D5, insert the following formula >> press Enter key >> use the Fill Handle tool.

8.2 Subtract Time Under or Over 24 Hours

The formula used in the previous method is not effective for times over 24 hours. We can apply a formula using simple arithmetic operators that will apply to times values both under and over 24 hours.

Insert the following formula in Cell D5 >> press the Enter key >> drag down the Fill Handle icon.

Here, the value 24 represents the number of hours in a day.

9. Subtract Minutes in Excel

The process of subtracting minutes from a time value is similar to subtracting hours.

9.1 Subtract Time Under 60 Minutes

Insert the formula below in cell D5 >> press Enter key >> use the Fill Handle tool.

9.2 Subtract Time Under or Over 60 Minutes

For a more effective formula that applies to both under or over 60 minutes, in Cell D5, insert the following formula >> press Enter key >> drag down the Fill Handle icon.

Here, 1440 depicts the total minutes in a day.

10. Subtract Seconds in Excel

We can follow a similar process as the previous two methods to subtract seconds from a time value in Excel.

10.1 Subtract Time Under 60 Seconds

In cell D5, enter the following formula >> press Enter key >> drag down the Fill Handle tool.

10.2 Subtract Time Under or Over 60 Seconds

In Cell D5, insert the formula below >> press Enter key >> use the Fill Handle tool.

Here, the value 86400 represents the number of seconds in a day.

Download Practice Book

Download the practice book.

Conclusion

We have demonstrated 7 easy and quick ways to subtract time in Excel. I hope these methods will help you to subtract time easily in Excel. Furthermore, the practice book is also added at the beginning of the article. Download it to practice more. Last of all, if you have any queries or suggestions, feel free to comment below.

Subtract Time in Excel: Knowledge Hub

<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel