Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

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-11 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)

Column E contains formulas to calculate the pace, in minutes per mile. For example, the formula

in cell E2 is

=(C2*60*24)/B2

Columns F and G contain formulas that calculate the year-to-date distance (using column B) and

the cumulative time (using column C). The cells in column G are formatted using the following

number format (which permits time displays that exceed 24 hours):

[hh]:mm:ss