XL: How to Modify a Given Time Using TIME and MOD

 Last reviewed: February 2, 1998
Article ID: Q95530
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, time values are represented with serial numbers ranging from 0 (12:00:00 AM) to 0.999988426 (11:59:59 PM).

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: <enter any integer number here>
   A2: Minutes  B2: <enter any integer number here>
   A3: Seconds  B3: <enter any integer number here>
   A4:    Time  B4: <enter the time of day to subtract from>
   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 wanted to subtract 25 hours and 10 minutes from 12:00 PM, your table would look like the following example: 

   A1: Hours    B1: 25
   A2: Minutes  B2: 10
   A3: Seconds  B3: 0
   A4: Time     B4: 12:00 PM
   A5: Less     B5: 1:10
   A6: Result   B6: 10:50 AM
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.

MORE INFORMATION

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 ensure that the result is a positive time value. The MOD function returns the remainder after dividing the result by 1. Since the remainder is always the same sign as the divisor, and the possible remainders are from 0 to .99999999, this always produces a correct time value.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 222, 433, 278 "Microsoft Excel Function Reference," version 2.10, pages 123, 236, 155 "Microsoft Excel Functions And Macros," version 2.21, pages 307-372

Additional query words: 98 97 7.00 5.00 3.00 4.00 4.00a XL98 XL97 XL7 XL5
XL4 XL3
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,5.0,5.0c,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,98
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.