Updated May 22, 2023
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 use a few examples to Subtract time in Excel.
Example #1
We have data where a person was assigned some tasks. We need to measure and calculate the time required to complete each task. By this, we can assign the tasks as per the required time.
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, we need to calculate the time taken to complete tasks.
For Subtracting Time in Excel, go to the cell where we need to see the output of subtracted time and type the “=” 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 coming 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 requirements. Now from Category, go to the Custom option and select [h]:mm. Once selected, click on, Ok, as shown below.
After clicking OK, we will get the subtracted time in HH:MM format. This will indicate the total time required 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 on some Airline arrival and Departure time in the 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. The MOD function produces a positive value even when the expected output is negative. But for exactness, we need to select the Departure time and arrival time. And separate them with a minus sign. And for the 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 Travel Time output data is in the real-time format 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. Directly 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 requirements. Now from Category, go to the 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 get the difference of it.
- This is mainly used when we need lap time, such as racing and flight/bus travel time.
- By subtracting the time and converting its format using “Format Cells,” we can determine its duration to cover a distance or complete a task or work.
- We can select any time format as per our requirements.
Cons of Subtract Time In Excel
- It doesn’t directly give the actual time difference by subtracting; instead, converting it to another 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 to get the difference.
- This process can calculate or subtract the difference in any number type.
- Only subtracting the time in excel needs extra steps to get the actual difference in time; the rest can be done by directly 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
