### Method 1. Subtracting Time between Two Cells to Get the Elapsed Time

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

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

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

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

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

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

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

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

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