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

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.