Subtract Time in Excel

How to Subtract Time in Excel? (With Examples)

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.

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

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:

Subtract time Example 1

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 the total time (in hours) taken by these students to complete the assignment in cells D2, D3, D4.

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


    Subtract time Example 1-1

  2. we get the result as 3.00.


    Subtract time Example 1-2

  3. Drag the formula from C2 to C4.


    Subtract time Example 1-3

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:

ST Example 2

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 the total time (in hours) taken by these students to complete the assignment in cells D2, D3, D4.

Subtract time Example 2-1

we got the result as given below:

Subtract time Example 2-2

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.

The following steps illustrate how to apply custom number format in excelCustom Number Format In ExcelIn Excel, Custom Number Format refers to changing the format of the numeric values as per your preferences to make the data look more visually appealing. This includes formatting large numbers in thousands or millions, formatting dates to only display the month names etc. read more:

  • Click on the ‘Home’ tab and expand the ‘Number Format’ dropdown. Click on ‘More Number Formats.’
ST Example 2-3
  • Select ‘Custom’ and in the ‘Type’ box, select: ‘dd-mm-yyyy hh:mm AM/PM’ and click ‘OK.’
St Example 2-4

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.

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 under 24 hours:

Example 3

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

Subtract time Example 3-1
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 the TIME () function also gives the same result.

Subtract time Example 3-2

Case 2) Time to be subtracted above 24 hours:

Subtract time Example 3-3

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 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 a result.

Subtract time Example 3-4

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

  • We can add hours/minutes/seconds to a DateTime in Excel using custom number formats or custom number formats that 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.
Example 3-5
  • If the Finish Time is greater than the Start Time, then the time difference is displayed as a negative number.
Example 3-6
  • 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.

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 a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>