This step-by-step article shows you how to add and subtract any number of hours, minutes, or seconds from a specific time
of day.
In Microsoft Excel 2000, time values are represented with serial numbers
that range from 0 (12:00:00 A.M.) to 0.999988426 (11:59:59 P.M.).
How to Add or Subtract Time
To subtract any number of hours, minutes, or seconds from a specific time
of day, create a table similar to the following:
A1: Hours B1: <type any integer number here>
A2: Minutes B2: <type any integer number here>
A3: Seconds B3: <type any integer number here>
A4: Time B4: <type the time of day to subtract from here>
A5: Less B5: =TIME(B1,B2,B3)
A6: Result B6: =MOD(B4-B5,1)
: To add the time to a specific time of day, change the formula in cell B6 to:
For example, if you want to subtract 25 hours and 10 minutes from
12:00 P.M., your table should look like the following example:
A1: Hours B1: 25
A2: Minutes B2: 10
A3: Seconds B3: 0
A4: Time B4: 12:00 P.M.
A5: Less B5: 1:10
A6: Result B6: 10:50 A.M.
In this table:
- Hours is the numbers of hours to subtract.
- Minutes is the number of minutes to subtract.
- Seconds is the number of seconds to subtract.
- Time is the initial time that you are subtracting from.
- Less is the total hours, minutes, and seconds being subtracted.
- Result is the time value result of the formula.
This formula uses the TIME function to convert integer numbers to
time values. If you want to add or subtract only hours, set the
minutes and seconds arguments in the TIME function to 0 (zero).
The MOD function is used to make sure that the result is a positive time value. The MOD function returns the remainder after dividing the result by 1. Because the remainder is always the same sign as the divisor, and the possible remainders range from 0 to .99999999, this always produces a correct time value.