## How to subtract time in Excel?Subtracting a time from another time is a type of operation of calculating time difference. Time difference can be a useful operation of Excel. So, the user should know how to perform it. Suppose that you have a task to complete within the given time period. Now, you want to know much hours work has been done from the total hours. You can find it by subtracting the work done hours from total hours. You will learn the time manipulation operation in an Excel sheet with the help of this chapter. You will also explore the time functions offered by MS Excel. ## How to calculate time difference?Excel offers more than one formula to get the time difference. The users need start time and end time to calculate the time difference. Or, if they want to calculate the remaining time from the total assigned hours, they need the total assigned hours and how much hours work has been done. To make the information more informative, you need to apply the formatting to your resultant value (time difference). The calculated time can be in hours, minutes, and seconds. You have to set in which format you want to get the time difference. ## Custom time formattingThe custom time formatting can be applied to data from the
Below are the different methods for subtracting the time from another. - Example 1: Subtract one time from other
- Example 2: Calculate and set the time difference using TEXT function
- Example 3: Subtract time and get remaining time for task
- Steps to find remaining work hours
- Steps to find remaining work minutes
- Steps to find remaining work seconds
- Example 4: Subtract AM/PM time
## Example 1: Subtract one time from otherThe first method to subtract a time from another is very simple. For this example, we have taken the start time and end time. We will calculate the time difference for this dataset and elapse the resultant into different time formats.
The resultant time difference data is in h:mm:ss format, i.e., 02:02:29, which means 2 hours, 2 minutes, and 29 seconds.
Sometimes, you see that the time elapsed as hash marks (#####). This can be because of two reasons. One is your cell is not wide enough to store the result or another can be that your resultant time difference is a negative value. If you ever get the hash mark (#####) because of a negative time value, you can display these values. We have described the way to display the negative value in this chapter below. ## Example 2: Calculate and set the time difference using TEXT functionIn the above method, you had manually set the custom time formats for different date differences. We will now use the TEXT format using which we will set the time format while calculating time differences. So, you do not need to set the date format manually. We will calculate the time difference as well as assign the specific time format for each time difference. You will find this technique simpler than the above method. The difference formula could be end time - start time, i.e., B2-A2. Its calculated result will be a string value. TEXT formula with time format Following are the codes for applying the custom formatting:
=TEXT(B2-A2, "h")
=TEXT(B2-A2, "h:mm")
=TEXT(B4-A4, "h:mm:ss")
In this way, you can specify the format for the calculated difference of time. So, you do not need to set the time format explicitly. ## Example 3: Subtract time and get remaining time for taskSuppose that we have some tasks stored in an Excel sheet for which we have taken the assigned hours for each task and the working hours how much time work has been done. We will subtract time for the work done from total assigned hours for the task and find how much hour's work remains. See the Excel sheet data for this scenario. The Excel user can use the following formula on data to get the time difference in a single unit.
To get the time difference in hours: This is a very interesting formula whenever you need to find how many hours of work is remaining from total assigned hours. Using this formula, we can find how many hours of work have remained. We also have the formula for minutes and seconds.
To get the time difference in minutes:
To get the time difference in seconds: Now, implement these formulas to the data that we had created above. But remember that after applying these formulas, you have to set the resultant column format to ## Steps to find remaining work hours
=(B2-C2)*24
General type for the selected data on this panel. Then, click OK.
In the same way, we will perform the same steps with task2 and task3 data to get the remaining time in minutes and seconds for the task to be done. ## Steps to find remaining work minutesThis time we will calculate the remaining time of task2 in minutes rather than hours.
=(B3-C3)*1440
General type for the selected data. Then, click OK.
## Steps to find remaining work secondsThe last is to calculate the remaining time work in seconds instead of minutes or hours.
=(B4-C4)*86400
General type for the selected data. Then, click OK.
These all methods are for different scenarios and they also used different formulas in each. You have noticed that - we are subtracting the time in all these formulas to get the time difference result. Hence, you can use whatever method you need. ## Example 4: Subtract AM/PM timeTill now, we had time in which AM or PM was not defined. But what if when the time meridian is also defined along with time? How does subtraction take place when you subtract time from another. So, let's see an example for this too.
We have applied h:mm AM/PM on this data to define the date in this time meridian format.
=B2-A2
So, we will change the format from h:mm AM/PM to h:mm format.
h:mm type for the selected data. Then, click OK.Now, see the meridian has been removed from the time.
This is because the resultant time difference is a negative value and time cannot be negative. So, Excel returned hash code (######) as an error.
In MS Excel, there is a way to display the negative time value (####) code. You have to change the Excel date system to 1904 date system. By setting the 1904 date system, -negative time will start displaying. Follow the steps to change the date system to 1904:
Next TopicExcel Icon Sets |