Microsoft Office: Rounding time in Excel

By J. Carlton Collins, CPA

Q. Our firm bills clients in six-minute increments, so I've started tracking my beginning and ending time for each job in Excel. Is it possible to round my time calculations to the nearest six-minute increment (a 10th of an hour) in Excel?

A. You can round your time calculations to the nearest 10th of an hour, or six-minute increment, as follows. You must break down the time values into hours, minutes, and seconds (using the HOUR, MINUTE, and SECOND functions), round the minutes and seconds (using the MROUND function), and then rebuild your time value (using the TIME function). For example, the time values pictured in column C below have been rounded to the nearest six-minute increment in column D using the ­following formula:


The data in column C are formatted numerically; the data in column D are formatted in hours and minutes so you can see that the resulting minutes are evenly divisible by 6. The key to this solution is that Excel's MROUND function returns a number rounded to a specified multiple (6 in this example). Note that the MROUND function always rounds up, away from zero. Therefore, in this example, if the time value being rounded happened to be two minutes away from a multiple of six minutes, the MROUND function would round the resulting value up, not down. You can download the worksheet used in this example at


About the author

J. Carlton Collins ( is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to We regret being unable to individually answer all submitted questions.


Building client loyalty with payroll services

In this report, CPA experts detail their tactics for performing successful payroll services, how to mitigate risk in the process, and the impact payroll can have as a value-added service.


Using drones to enhance audits

Hermann Sidhu, CPA, global assurance digital leader at EY, walks us through EY’s exciting new project to use drones to help audit large warehouses and outdoor inventories.