Explanation
MROUND rounds to nearest values based on a supplied multiple. When you supply “0:15” as the multiple, Excel internal converts 0:15 into 0.0104166666666667, which is the decimal value that represents 15 minutes, and rounds using that value.
You can also express 15 minutes in a formula with this formula:
=15/(60*24)
The formula above divides 15 by 1440, which is the number of minutes in one day. So, to Excel, these formulas are identical:
=MROUND(B6,"0:15")
=MROUND(B6,15/(60*24))
Round to other time intervals
As you would expect, you can use the same formula to round to different time intervals. To round to the nearest 30 minutes, or nearest 1 hour, use these formulas
=MROUND(time,"0:30") //nearest 30 minutes
=MROUND(time,"1:00") //nearest 1 hour
Always round up
To always round up to the nearest 15 minutes, you can use the CEILING function :
=CEILING(B6,"0:15")
Like MROUND, the CEILING function rounds to a nearest multiple. The difference is that CEILING always rounds up. The FLOOR function can be used to always round down
Explanation
In the example, cell C6 contains this formula:
=ROUND(B6,-3)
The value in B6 is 1,234,567 and the result is 1,235,000.
With the ROUND function, negative numbers for the second argument round to the left of the decimal and positive numbers round to the right of the decimal.
In this case, by supplying -3, we are telling ROUND to round the number to the 3rd place on the left – the 1000’s place.
