Round time to nearest 15 minutes

Round time to nearest 15 minutes

Generic formula 

=MROUND(time,"0:15")

Explanation

If you need to round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. To round to the nearest 15 minutes, just supply "0:15" for the multiple.

How this formula works

The formula in C6 is:

=MROUND(B6,"0:15")

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.

You can also express 15 minutes in a formula with this formula:

=15/(60*24)

The formula above pides 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))

Rounding 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

Round time to nearest 15 minutes

Generic formula 

=MROUND(time,"0:15")

Explanation

If you need to round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. To round to the nearest 15 minutes, just supply "0:15" for the multiple.

How this formula works

The formula in C6 is:

=MROUND(B6,"0:15")

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.

You can also express 15 minutes in a formula with this formula:

=15/(60*24)

The formula above pides 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))

Rounding 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