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)
Search JabSto ::




Custom Search