In this post, I am going to show you how to create a formula to convert time duration into tenths of an hour using Excel.
Why would you need this?
The most likely reason you might need this is for reporting payroll hours worked. Our organization defines the conversion of minutes to decimal hours in Appendix C – Leaves / Absence Reporting.
A simplified scenario
Data entry could be captured simply as [StartTime] and [EndTime] with [HrsWorked] being the duration between the two times; [EndTime] – [StartTime].
The result of
B2 - A2 in Cell C2, when formatted as ‘General’ is 0.09305556. This numeric value is the serial number format of ‘Time’ represented as a decimal.
Adjusting to proper hours of a day
Use the PRODUCT ( ) Function as
=PRODUCT(B2-A2,24) in Cell C2.
MROUND to desired multiple
Use the MROUND ( ) Function to return a number rounded to the nearest 6th minute. In Cell C2,
Interestingly, the returned value has not changed.
ROUNDUP to finish it off
Lastly, wrap the entire formula in the ROUNDUP ( ) Function. Cell C2 should now be
The hours worked are properly rounded up to the nearest 6th minute. Make sure you double check the calculation(s), this is payroll after all.