HOW TO: Modify a Particular Time with the TIME and MOD Functions in Excel 2000 (214095)

SUMMARY

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.).

back to the top

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)
				

NOTE: To add the time to a specific time of day, change the formula in cell B6 to:

   B6: =MOD(B4+B5,1)
				

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.

back to the top