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:


Generic formula



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:


The formula above divides 15 by 1440, which is the number of minutes in one day. So, to Excel, these formulas are identical:


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:


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

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.