Excel Subtract Time (Table of Contents)
Subtract Time in Excel
Subtracting time in excel is easy task to do but to keep the required format takes a little step further. Subtracting the time can be done selecting the cell with recent time then subtracting it with older time lap. There is no special function in excel available to use subtracting time, in fact it is the combination of different functions. It gives us the exact difference in time.
How to Subtract Time in Excel?
Let’s understand how to Subtract time in Excel by using a few examples.
Example #1
We have a data where a person was assigned some tasks. We need to measure and calculate that how much time is required to complete a task. By this we can assign the tasks as per actual required time for it.
Below is the table where for 3 tasks for start time is mentioned in column B and end time is mentioned in column C. Now for mentioned tasks we need to calculate the time taken to complete them.
For Subtracting Time in excel, go to the cell where we need to see the output of subtracted time and type “=” sign (Equal). And select the time in column C first and then select the select time in Column B. Now separate the time with minus (“-“) which will subtract the time as shown below.
Now press enter to see the result.
Drag or copy the applied formula to below respective cells as shown below.
As we can see in above screenshot, the time difference of each start and end time is being coming as the time shows in clock considering AM/PM. Now we need to convert this subtracted time to see how much time is actual and real difference. For this select the cell in Total Time Taken column D and click right on it to get the menu list. From there select Format Cells as shown below.
This will take us to the Format Cell windows. Here we can change and update the existing format as per our requirement. Now from Category go to Custom option and select [h]:mm. Once selected, click on Ok as shown below.
Note: If [h]:mm time format is not found, then we can change any of the similar format or else create a new format.
After clicking on OK, we will get the subtracted time in HH:MM format. This will show exactly how much time is taken to complete all the assigned tasks.
Example #2
There is another method of subtracting time in excel. This we will do with the function MOD in excel. We have data of some Airline arrival and Departure time which is in the format of HH:MM:SS format as shown below.
Now for calculating or subtracting time in excel type “=” sign in column D Travel Time and search and select MOD Function as shown below.
As we can see the syntax number and divisor are part of it. MOD gives the positive value even if the output is supposed to come in negative. But for exactness, we need to select the Departure time and then arrival time. And separate them with minus sign. And for divisor put 1, because we do not need to divide it.
Now press enter to see the result.
Drag or copy the applied formula to below cells as shown below.
As we can see the output data of Travel Time is in the format of real-time with time category AM/PM. Now to change the format into actual time difference select the data and click right as we did in example-1 to get list. Now from that list select Format Cells as shown in below screenshot.
Now in Format Cell windows. Here we can change and update the existing format as per our requirement. Now from Category go to Custom option and select [h]:mm. Once selected, click on Ok as shown below.
After that we will get actual time in HH:MM format. And this shows time taken by different Airlines to complete a journey.
Pros of Subtract Time in Excel
- This is the easiest way to subtract time and getting the difference of it.
- This is used mostly when we need to get lap time such as in racing, flight/bus travel time.
- Subtracting the time and later converting its format gives from Format Cells gives use the how much time is being taken for covering the distance or completing any task or work.
- We can select any time format as per our requirement.
Cons of Subtract Time In Excel
- It directly doesn’t give the actual time difference by subtracting, instead of converting it to other format is required.
Things to Remember
- Select the time format as in HH:MM Format or MM:SS format, depending on kind of time difference we have.
- Data input for subtracting the time in excel should be in TIME format. And this Time format can be of any other type.
- If you are subtracting the time as explained in example-2, always use double brackets, as in first bracket we need to select number range for getting the difference.
- This process can be used for calculating or subtracting the difference in any number type.
- Only subtracting the time in excel needs extra steps to get the actual difference in time, rest can be done just by direct subtracting the cell numbers.
Recommended Articles
This has been a guide to Subtract Time in excel. Here we discuss How to Subtract Time in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
