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.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.

SPONSORED

Building process maps: Template and instructions

Documenting your financial close process and finding opportunities for automation are more important than ever. Our customizable slide deck has instructions, a risk assessment questionnaire, and bonus checklists that will help you map out your process.