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.