Rounding a time into 15 minutes increments in Excel

All the rounding functions work well for situations where you need to round a time up or down to, for example, the closest 15 minutes e.g.. 10h33 must become 10h30 or 10h45. The trick here is not the rounding part but understanding how Excel handles time.

Excel treats time as a decimal of a 24 hour day. So if I want to say 6am to Excel, I can type in 0.25  (25% of a 24 hour day). Using this logic you can now use all the various rounding functions to achieve what you want.

So for the initial example, in order to round in 15 minute intervals you need to know what 15 minutes is in Excel speak. A 24 hour day consists of 1 440 minutes (60 minutes x 24 hours), so 15 minutes can be represented by 15/ 1440 or the number 0.010416666…. Try it. Type =15/1440 into a cell, push enter, and format as time and you will see a time of 15 minutes past 12.

Now you can use the various rounding functions with this as the driver. Format in time and you will see that it works.

Full time equivalent calculations in Excel