Microsoft Office Tutorials and References

In Depth Information

=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 (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

might want to create a list of the finish times for a race or record the time you spend jogging each day. Such

times don't represent a time of day. Rather, a value represents the time for an event (in hours, minutes, and

seconds). The time to complete a test, for instance, might 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 AM, which works fine (just make sure that you format the cell so it

appears as you like). When you enter such times that do not 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.

Figure 6-12 shows an example of a worksheet set up to keep track of someone's jogging activity. Column A

contains simple dates. Column B contains the distance, in miles. Column C contains the time it took to run the

distance. Column D contains formulas to calculate the speed, in miles per hour. For example, the formula in cell

D2 is

=B2/(C2*24)