Microsoft Office Tutorials and References
In Depth Information
Rounding time values
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.
16
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 1,440 (to get total minutes). This value is
passed to the ROUND function, and the result is divided by 1,440. 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 .
The following formula rounds the time in cell A1 to the nearest 15 minutes (a quarter of an
hour):
=ROUND(A1*24/0.25,0)*(0.25/24)
In this formula, 0.25 represents the fractional hour. To round a time to the nearest 30
minutes, change 0.25 to 0.5 , as in the following formula:
=ROUND(A1*24/0.5,0)*(0.5/24)
Working with non-time-of-day values
Sometimes, you may want to work with time values that don’t represent an actual time of
day. For example, you may want to create a list of the ﬁ nish times for a race or record the
amount of time you spend in meetings each day. Such times don’t represent a time of day.
Instead, a value represents the time for an event (in hours, minutes, and seconds). The
time to complete a test, for example, may be 35 minutes and 45 seconds. You can enter that
value into a cell as:
00:35:45
Excel interprets such an entry as 12:35:45 a.m., which works ﬁ ne. (Just make sure that you
format the cell so that it appears as you like.) When you enter such times, which don’t have
an hour component, you must include at least one zero for the hour. If you omit a leading
zero for a missing hour, Excel interprets your entry as 35 hours and 45 minutes.
Search JabSto ::

Custom Search