Excel Subtract Time (Table of Contents)
Subtract Time in Excel
Subtracting time in excel is an easy task, but keeping the required format takes a little step further. Subtracting the time can be done by selecting the cell with recent time then subtracting it with the 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 data where a person was assigned some tasks. We need to measure and calculate how much time is required to complete a task. By this, we can assign the tasks as per the actual required time for it.
Below is the table where for 3 tasks, for start time is mentioned in column B, and the 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 a 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 the above screenshot, each start and end time difference is being come as the time shows in the clock considering AM/PM. Now we need to convert this subtracted time to see how much time is the 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.
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 the “=” 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 a 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 the below cells, as shown below.
As we can see, the output data of Travel Time is in the format of real-time with the 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 the list. Now from that list, select Format Cells as shown in the 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 the actual time in HH:MM format. And this shows the 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 mostly used when we need to get lap time, such as racing, flight/bus travel time.
- Subtracting the time and later converting its format gives from Format Cells gives use of how much time is being taken to cover the distance or complete 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 the time difference.
- 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 the first bracket, we need to select a 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 a downloadable excel template. You can also go through our other suggested articles –
