Microsoft Office Tutorials and References
In Depth Information
Rounding Time Values
Rounding Time Values
You might need to create a formula that rounds a time to a particular number of minutes. For
example, you might need to enter your company’s time records rounded to the nearest 15
minutes. This tip presents examples of various ways to round a time value.
The following formula rounds the time in cell A1 to the nearest minute:
=ROUND(A1*1440,0)/1440
This formula works by multiplying the time by 1440 (to get the total number of minutes). This
value is passed to the ROUND function, and the result is divided by 1440. For example, if cell A1
contains 11:52:34, the formula returns 11:53:00.
The following formula is similar, 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 (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)
 
Search JabSto ::




Custom Search