Excel 2010 Bible

Chapter 12: Working with Dates and Times

279

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 pres-

ents 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.

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 finish 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. Rather, a value repre-

sents 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 fine. (Just make sure that you format

the cell so that it appears as you like.) When you enter such times that do not have an hour com-

ponent, 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.