Microsoft Office Tutorials and References
In Depth Information
Figure 6-11: This worksheet converts dates and times between time zones.
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:
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
The following formula resembles this example, except that it rounds the time in cell A1 to the nearest hour: