# How to Subtract Time in Excel -10 Methods

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

`=MINUTE(D5-C5)`

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

`=SECOND(D5-C5)`

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

• This is the final output.

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

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

## Subtract Time in Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF