Subtracting Time in Excel
I wish this article could be shorter by simply changing a + sign to a – sign in the formulas and calling it a day. Unfortunately, Excel doesn’t understand negative time the way we need it to in order to subtract time in Excel. Fortunately Excel allows us to subtract time another way.
Excel stores time as a fraction of 24 hours, so 12:00PM (or noon) is stored as 0.5.
If we have a start time of 9:00AM, and an end time of 2:30PM, we want to subtract the two to figure out how much time has passed. We can use a very basic formula to do this.
=(End Time - Beginning Time)*24
In our example, this would give us 5.5 because 2:30PM is 5.5 hours past 9:00AM.
If we don’t want to format the total time as a fraction of an hour, we can use the same [h]:mm format from the Adding Time example to get a result of 5:30 for five hours and 30 minutes. However in order to do this we will have to change the formula used in the total by removing the multiplication of 24 hours. To do this simply delete *24 from the equation.
Finally, when we have a given time that we want to subtract time from, we can use the TIME function exactly the same way as we did in the Adding Time example.
In the previous example we wanted to know what time it would be 8 hours after 9:00AM. If we wanted to find 8 hours before 9:00AM we can simply change the + sign to a – and the formula will work just the same.
