How to subtract time in Excel

It may appear to be a simple notion to subtract some hours from a time value in Excel, but it is not as straightforward as it appears. This article will teach us how to subtract hours from a time value and how to subtract two different time values.

Example 1: Subtracting Hours from a Time Value

Consider this data set in which we have some time values marked as Start Time and we will calculate the End Time by subtracting some hours to the start time.

Before learning how to subtract numbers from the time values, we’ll try to understand the problem associated with subtracting simple numbers to time values. So let’s do this step by step.

Step 1 – Subtracting numbers to time to see the problem

• The first column in our data set is formatted as time and we can see that the day and night time is differentiated by AM and PM properly.
• The problem associated with subtracting hours from a time value is that if we simply subtract a number considering that it is time then it won’t be subtracted properly from the time value and we’ll get “########” which shows that we got a negative result. However, even if we use ABS() to get the values only, we get incorrect results. Let’s see what happens when we try to subtract numbers from time values.

Step 2 – Format Numbers to reflect time values

• So to resolve the problem faced in the last step, we’ll have to format i.e. to write the numbers in proper format so that Excel can recognize the numbers as time values.
• Let’s do this by writing all numbers in the format shown below.
• Now that we have formatted the values properly, we can add these into time values without any further ado. Now all values were added properly with start time values.

Example 2: Subtracting Time Values from each other

While working with time values often we have to subtract them from each to find out the elapsed time between two events. Let’s see the dataset below where we have the Time In & Time Out details of the employees and we wish to find out the total time they worked for in a day.

Step 1 – Subtract the values directly

• We can see that the data values are already in time format showing the time in and time out. We can directly subtract them and get our results easily.

So using the above stated two methods, we can subtract hours from time values and also subtract time values from each other.