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.

**Table of Contents**Expand

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

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

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

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

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

`=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 in 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 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:

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

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

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

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

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

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

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

`=C5-TIME($F$5,0,0)`

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

`=C5-($F$5/24)`

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.

`=C5-TIME(0,$F$5,0)`

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

`=C5-($F$5/1440)`

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.

`=C5-TIME(0,0,$F$5)`

#### 10.2 Subtract Time Under or Over 60 Seconds

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

`=C5-($F$5/86400)`

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