Microsoft KB Archive/214095

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

Article ID: 214095

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214095



For a Microsoft Excel 98 and earlier version of this article, see 95530.

IN THIS TASK
SUMMARY
 * How to Add or Subtract Time

REFERENCES



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:  A2: Minutes B2:  A3: Seconds B3:  A4:   Time  B4:  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:
 * is the numbers of hours to subtract.
 * is the number of minutes to subtract.
 * is the number of seconds to subtract.
 * is the initial time that you are subtracting from.
 * is the total hours, minutes, and seconds being subtracted.
 * 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

