Subtract Time In Excel

The Subtract Time in Excel is a feature to find the time difference between the two time values, i.e., the start and end times. We can perform Excel Subtract Time of time values less than 24 hours by using the ‘-’ operator. However, the time values that on subtraction exceed 24 hours/60 minutes/60 seconds are ignored by Excel. In such scenarios, we use the Custom Number Format.

For example, we have the start and end examination time for a particular student. Enter the formula =(C2-B2)*24 in cell D2, to find the time taken or the time difference.

The output is 3 hours, as shown above.

• The Subtract Time in Excel considers two valid time values and helps users find the difference between them. We must always multiply the result by 24 to convert it into hours so that Excel doesn’t ignore even the seconds if the time difference exceeds 24 hours.
• The end time must always be larger than the start time and the values must be positive values in custom time formats.
• 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 lesser or earlier than the Start Time, then the time difference is displayed as a negative number.

We do not have an inbuilt function to Subtract Time in Excel. However, we can construct some formulas and find the time difference as follows:

1. Ensure we have a minimum of two values, start and end time.
2. Type =(End time – Start Time)*24, as cell reference or cell values.
3. Press the “Enter” key to calculate the time difference.

We will consider examples to Subtract Time in Excel.

Example #1

In the given dataset, we have the Start and Finish Time of doing an assignment by some studentsin cells B2, B3, B4, and C2, C3, C4, respectively. Let us calculate the total time taken by these students to complete the assignment in cells D2, D3, and D4.

The steps to calculate the time difference are as follows:

1. So, we subtract the two given time values using the “–” operator and then multiply the result by 24 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.

   
   

2. We get the result as 3.00.

   
   

3. Drag the formula from C2 to C4.

   
   

Example #2

Now, let us Subtract 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 and Finish Time taken by three students to complete an assignment are provided in cells B2, B3, B4, and C2, C3, and C4, respectively, and we wish to calculate the total time (in hours) taken by these students to complete the assignment in cells D2, D3, and D4.

We get 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.

The following steps illustrate how to apply the custom number format in excel:

• Click the ‘Home’ tab and expand the ‘Number Format’ drop-down. Click on the ‘More Number Formats…’ option, as shown below.

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

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

Thereafter, we subtract the two given time values using the ‘- operator and then multiply the resultant value by 24 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

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 result from the given time:

#Case 1 - Time to be subtracted under 24 hours:

We can see that Given Time is present in cells A2 and B2, and 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)

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

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

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

#Case 2 - Time to be subtracted 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 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’, we get ‘26-03-2019 13:56’ as a result.

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

• The 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)

• 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.
• If, after applying the custom formatting, a cell displays a sequence of ‘#####,’ then this is due to the fact that the width of the cell is not sufficient enough to display the date-time value.