Excel formula: Round time to nearest 15 minutes

Generic formula

=MROUND(time,"0:15")

Explanation

To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. In the example shown, the formula in C6 is:

=MROUND(B6,"0:15")

How this formula works

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

Author

Dave Bruns

Related functions

Excel MROUND Function

The Excel MROUND function returns a number rounded to a given multiple. MROUND will round a number up or down, depending on the nearest multiple.

Excel CEILING Function

The Excel CEILING function returns a given number rounded up to a specified multiple. For example, =CEILING(A1,5) could be used to round a price in A1 to the nearest 5 dollars. CEILING always rounds up.

Excel FLOOR Function

The Excel FLOOR function rounds a given number down to the nearest specified multiple. FLOOR works like the MROUND function, but FLOOR always rounds down.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.