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.

SPONSORED REPORT

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.