How to Subtract Time in Excel (7 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.


Download Practice Book

Download the practice book.


7 Ways to Subtract Time in Excel

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.

Subtract Time between Two Cells to Get the Elapsed Time

Read More: How to Calculate Elapsed Time in Excel (8 Ways)


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:
=D5-C5

Subtract Time between Two Cells to Get the Elapsed Time

  • Now, hit Enter.

Subtract Time between Two Cells to Get the Elapsed Time

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 hour properly.

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

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time


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:
=IF(D5>=C5, D5-C5, D5+1-C5)

Subtract Time between Two Cells to Get the Elapsed Time

  • Next, press Enter to see the result.

Subtract Time between Two Cells to Get the Elapsed Time

Here, the IF Function will first check if the value of D5 is greater or equal than 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.

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time


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:
=MOD((D5-C5),1)

Subtract Time between Two Cells to Get the Elapsed Time

  • Next, press Enter to see the result.

Subtract Time between Two Cells to Get the Elapsed Time

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

  • Again, change the Time Format.

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time


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:
=TEXT(D5-C5, “h:mm:ss”)

Subtract Time between Two Cells to Get the Elapsed Time

  • Hit Enter to see the result.

Subtract Time between Two Cells to Get the Elapsed Time

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 as hours, minutes, & seconds format.

  • Similarly, if you want to display only hours and minutes, then, type the formula:
=TEXT(D5-C5,“h:mm”)

Subtract Time between Two Cells to Get the Elapsed Time

  • Press Enter to see the result.

Subtract Time between Two Cells to Get the Elapsed Time

  • Again, if you want to display only hours, then, type the formula:
=TEXT(D5-C5,“h”)

Subtract Time between Two Cells to Get the Elapsed Time

  • Now, press Enter to see the result.

Subtract Time between Two Cells to Get the Elapsed Time

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

Subtract Time between Two Cells to Get the Elapsed Time


2. Subtract Time with TIME Function in Excel

There are some times 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:
=C5-TIME(1,30,0)

Subtract Time with TIME Function in Excel

  • Now, press Enter to see the result.

Subtract Time with TIME Function in Excel

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.

Subtract Time with TIME Function in Excel

Read More: How to Subtract Military Time in Excel (3 Methods)


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.

Calculate and Display Negative Time Difference in Excel

To fix this problem, follow the steps below.

STEPS:

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

Calculate and Display Negative Time Difference in Excel

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

Calculate and Display Negative Time Difference in Excel

  • Click OK to see results like below.

Calculate and Display Negative Time Difference in Excel

  • Alternatively, you can use the below formula in Cell E5.
=IF(D5-C5>0, D5-C5, “-” & TEXT(ABS(D5-C5),”h:mm”))

Calculate and Display Negative Time Difference in Excel

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

Calculate and Display Negative Time Difference in Excel

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.

Read More: How to Subtract and Display Negative Time in Excel (3 Methods)


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:
=(D5-C5)*24

  • 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:
=INT((D5-C5)*24)

  • 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.
=(D5-C5)*1440

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

  • To convert into minutes, multiply the formula by 86400.
=(D5-C5)*86400

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


Similar Readings:


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:
=HOUR(D5-C5)

  • 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 minute part at the output, use the MINUTE Function and type the formula:
=MINUTE(D5-C5)

  • 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:
=SECOND(D5-C5)

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

  • Finally, we display the values separately like below.

Related Content: How to Calculate the Duration of Time in Excel (7 Methods)


6. Use of NOW Function to Subtract 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:
=NOW()-C5

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.

Related Content: How to Subtract Date and Time in Excel (6 Easy Ways)


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:
=D5-C5

  • 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:
h "hours," m "minutes and" s "seconds"

  • Click OK to see results like below.

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

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo