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

### Method 4 – Subtracting Time and Displaying it in a Single Unit in Excel

**STEPS:**

- Select
**E5**and enter the formula:

`=(D5-C5)*24`

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

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

- To display the integer value only, use the
**INT Function**and enter:

`=INT((D5-C5)*24)`

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

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

- To convert the hours into minutes, multiply the formula by
**1440**.

`=(D5-C5)*1440`

- Press
**Enter**and use the**Fill Handle**to see the result.

- To convert the minutes into seconds, multiply the formula by
**86400**.

`=(D5-C5)*86400`

- Press
**Enter**and use the**Fill Handle**to see the result.

### Method 5 – Calculating the Time Difference in a Unit Ignoring Other Units

**STEPS:**

- Select a cell and enter the formula:

`=HOUR(D5-C5)`

- Press
**Enter**and drag down the**Fill Handle**to see the result.

Here, the **HOUR Function **subtracts the values of Cell **D5 **and **C5 **and display hours only.

- To see minutes only, use the
**MINUTE Function**and enter the formula:

`=MINUTE(D5-C5)`

- Press
**Enter**and use the**Fill Handle**to see the result.

- Use the
**SECOND Function**to see seconds only. Enter the formula:

`=SECOND(D5-C5)`

- Press
**Enter**and drag down the**Fill Handle**to see the result.

- This is the final output.

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

### Method 6 – Using the NOW Function to Subtract Time from Current Time

The **NOW Function **subtracts time from current time.

**STEPS:**

- Select
**D5**and enter the formula:

`=NOW()-C5`

The formula subtracts the value of **C5 **from the current time.

- Press
**Enter**and use the**AutoFill**to see the result.

### Method 7 – Subtracting and Displaying Time in Hours, Minutes and Seconds Units

**STEPS:**

- Select
**E5**and enter the formula:

`=D5-C5`

- Press
**Enter**.

- Go to the
**Home**tab and choose**Number Format**.

- In
**Format Cells**, select**‘Custom’** - In
**Type**, enter the text:

`h "hours," m "minutes and" s "seconds"`

- Click
**OK**to see the result.

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

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

### Method 8 – Subtracting Hours in Excel

To subtract the required hours from a given time,use the **TIME **function or basic arithmetic operators.

#### 8.1 Subtract Time Under 24 Hours

In **D5**, enter the following formula >> press **Enter** >> use the **Fill Handle** tool.

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

#### 8.2 Subtract Time Under or Over 24 Hours

This formula uses simple arithmetic operators and applies to time values both under and over 24 hours.

Enter the following formula in **D5** >> press **Enter** >> drag down the **Fill Handle**.

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

### Method 9 – Subtracting Minutes in Excel

#### 9.1 Subtract Time Under 60 Minutes

Enter the formula below in **D5** >> press **Enter** >> use the **Fill Handle** tool.

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

#### 9.2 Subtract Time Under or Over 60 Minutes

In **D5**, enter the following formula >> press **Enter** >> drag down the **Fill Handle**.

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

### Method 10 – Subtracting Seconds in Excel

#### 10.1 Subtract Time Under 60 Seconds

In **D5**, enter the following formula >> press** Enter** >> drag down the **Fill Handle**.

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

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

In **D5**, enter the formula below >> press **Enter** >> use the **Fill Handle**.

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

**Download Practice Book**

Download the practice book.

## Subtract Time in Excel: Knowledge Hub

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