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.

**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`

- 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 hour 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:

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

- Next, press
**Enter**to see the result.

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.**

- 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:

`=MOD((D5-C5),1)`

- Next, press
**Enter**to see the result.

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.**

- 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:

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

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

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

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

- Press
**Enter**to see the result.

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

`=TEXT(D5-C5,“h”)`

- 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 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)`

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

**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.

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.**

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

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

**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:**

**[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)****How to Calculate Average Handling Time in Excel (2 Easy Ways)****Use Time Format in Excel VBA (Macro, UDF, and UserForm)****How to Calculate Turnaround Time in Excel (4 Ways)****Calculate Hourly Rate in Excel (2 Quick Methods)**

### 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.