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:

=TIME(HOUR(C2), MROUND(MINUTE(C2)+SECOND(C2)/60,6),0)

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 CarltonCollins.com/Time.xlsx.

techqa-1

About the author

J. Carlton Collins (carlton@asaresearch.com) 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 jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

VIDEO

Excel walk-through: Sparklines

Want to liven up your spreadsheets with some color and graphical elements? Kelly L. Williams, CPA, Ph.D., shows how to use Excel sparklines, which illustrate data trends and patterns via small charts that fit in a single Excel cell.

PODCAST

What’s next for potential CPA licensure changes

A new model proposed by NASBA and the AICPA is designed with an eye on the future for newly licensed CPAs. The AICPA's Carl Mayes, CPA, provides background on the project and a look ahead to 2020.