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

Search JabSto ::

Custom Search