Microsoft Office Tutorials and References
In Depth Information
Time-Related Functions
Figure 6-10: This worksheet converts dates and times between time zones.
You can access the workbook shown in Figure 6-10, gmt conversion.xlsx , on the
companion CD-ROM.
This formula effectively adds x hours to the date and time in column A. If cell B1 contains a
negative hour value, the value subtracts from the date and time in column A. Note that, in some cases,
this also affects the date.
Rounding time values
You may need to create a formula that rounds a time to a particular value. For example, you may
need to enter your company’s time records rounded to the nearest 15 minutes. This section
presents examples of various ways to round a time value.
The following formula rounds the time in cell A1 to the nearest minute:
=ROUND(A1*1440,0)/1440
The formula works by multiplying the time by 1440 (to get total minutes). This value is passed to
the ROUND function, and the result is divided by 1440. For example, if cell A1 contains 11:52:34,
the formula returns 11:53:00.
The following formula resembles this example, except that it rounds the time in cell A1 to the
nearest hour:
=ROUND(A1*24,0)/24
If cell A1 contains 5:21:31, the formula returns 5:00:00.

Search JabSto ::

Custom Search