## Subtract Time in Excel

It has become quite easy to perform operations like add or subtract different time and date values with Excel. For subtraction of time values less than 24 hours, we can easily subtract these by using the ‘-’ operator. However, the time values that on subtraction exceed 24 hours/60 minutes/60 seconds are ignored by Excel. We use Custom Number Format in such cases.

### Examples of Subtract Time in Excel Formula

Below are the examples of Subtract time in excel formula.

#### Subtract Time Excel Example #1

If we are provided the Start Time and Finish Time of doing an assignment by some students (i.e we are provided the times at which students start and finish the assignment), and we wish to calculate the total time taken by these students to complete the assignment:

We can see that the Start Time and Finish Time is taken by three students to complete an assignment are provided in cells: B2, B3, B4, and C2, C3, C4 respectively, and we wish to calculate total time (in hours) taken by these students to complete the assignment in cells D2, D3, D4.

So, we subtract the two given time values using the ‘-operator, and then multiply the resultant value with 24 so as to get the number of hours taken to complete the assignment. This works quite easily as the subtraction value of given times is no more than 24.

we get the result as 3.00.

Drag the Formula from C2 to C4.

#### Subtract Time Excel Example #2

Now, let’s say we wish to subtract the time in Excel that is more than 24 hours when the start time and finish time of doing the assignment are given:

We can see that the Start Time and Finish Time is taken by three students to complete an assignment are provided in cells: B2, B3, B4, and C2, C3, C4 respectively, and we wish to calculate total time (in hours) taken by these students to complete the assignment in cells D2, D3, D4.

we got the result as given below:

These DateTime values (that cross midnight) are written using custom formats that can be used to display time intervals that exceed the standard time units’ length.

4.9 (1,353 ratings)

Following steps illustrate how custom number format can be applied:

- Click on the ‘Home’ tab and expand the ‘Number Format’ dropdown. Click on ‘More Number Formats’.

- Select ‘Custom’ and in the ‘Type’ box, select: ‘dd-mm-yyyy hh:mm AM/PM’ and click ‘OK’.

Now the next time we need this format, it would be saved in the ‘Type’ list.

Thereafter, we subtract the two given time values using the ‘- operator and then multiply the resultant value with 24 so as to get the number of hours taken to complete the assignment.

Similarly, if we wish to calculate minutes or seconds between two-time values, then we subtract the two-time values and multiply the resultant value with 1440 or 86400 (instead of 24) respectively.

#### Subtract Time Excel Example #3

Now, let’s say we wish to subtract the desired time interval from a given time: This can be done by dividing the number of hours, minutes, or seconds by the number of the corresponding unit in one day (24 hours, 1440 minutes, 86400 seconds) and then subtracting the resultant quotient from the given time:

**Case 1)** Time to be subtracted is under 24 hours:

We can see that Given Time is present in cell A2 and cell B2 contains a number of hours we wish to subtract from this. So, these two-time values are subtracted as follows:

Time Taken = Given Time - ( No of hours to be subtracted/24)

Now this result can also be achieved using TIME () function as follows: So we can see that when we subtract 4 hours from the given time: 16:00, we get the result as 12:00.

Time Taken = Given T000ime - TIME ( No of hours to be subtracted,0,0)

However, when we wish to subtract under 24 hours, only then TIME () function can be used. So we see that TIME () function also gives the same result.

**Case 2)** Time to be subtracted is above 24 hours:

We can see that Given DateTime is present in cell A4 and cell B4 contains a number of hours we wish to subtract from this. Now, these two-time values are subtracted as follows:

Time Taken = Given Time - ( No of hours to be subtracted/24)

**Case 3)** Time to be subtracted is above 60 minutes or 60 seconds: Hence, we find that this formula has no limitations to the number of hours we desire to subtract. So, when 26 hours (>24) are subtracted from the Given DateTime: ‘27-03-2019 15:56’, then we get ‘26-03-2019 13:56’ as the result.

Here, the two-time values are subtracted by using the same formula as above. The only difference in the formula would be that:

- A number of minutes that we desire to subtract from the given DateTime are divided by 1440 (when the time to be subtracted is above 60 minutes, as 1 day has 1440 minutes) and the formula would work as follows:

Time Taken = Given Time - ( No of minutes to be subtracted/1440)

- The number of seconds that we desire to subtract from the given DateTime is divided by 86400 (when the time to be subtracted is above 60 seconds as 1 day has 86400 seconds) and the formula would work as follows:

Time Taken = Given Time - ( No of seconds to be subtracted/86400)

### Things to Remember About Subtract Time in Excel Formula

- We can add hours/minutes/seconds to a DateTime in Excel using custom number formats or custom number formats can be used to format time values that are beyond 24 hours, or 60 minutes, or 60 seconds.
- The Custom time formats work only for positive time values.
- In order to get the complete number of hours after subtraction of time values, we can use the ‘INT’ function and round it off to the nearest integer.

- If the Finish Time is greater than the Start Time, then the time difference is displayed as a negative number.

- If after applying the custom formatting, a cell displays a sequence of ‘#####’, then this could happen due to the fact that the width of the cell is not sufficient enough to display the date time value.

You can Download this Subtract Time Excel Template here – Subtract Time Excel Template

### Recommended Articles

This has been a guide to Subtract Time in Excel. Here we discuss how to Subtract Time in Excel Formula with some examples and downloadable excel template. You may learn more about excel from the following articles –

- How to Use Chi-Square Test in Excel?
- Excel Change Text Case
- Running Total in Excel
- How to Add Time in Excel?
- TIME Excel Formula
- Time in VBA
- Create Timer in VBA

- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion

- Basic Microsoft Excel Training
- MS Excel 2010 Training Course: Advanced
- Microsoft Excel Basic Training
- Microsoft Excel 2013 – Advanced
- Microsoft Excel 2016 – Beginners
- Microsoft Excel 2016 – Advanced

## Leave a Reply