Minutes to Decimal Hours (tenths)

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 6^th minute. In Cell C2, =MROUND(PRODUCT(B2-A2,24),"0:06").

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 =ROUNDUP(MROUND(PRODUCT(B2-A2,24),"0:06"),1).

The result

The hours worked are properly rounded up to the nearest 6^th minute. Make sure you double check the calculation(s), this is payroll after all.