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.

Appendix C: Conversion table - Minutes to Decimal Hours
Appendix C: Conversion table – Minutes to Decimal Hours

A simplified scenario

Data entry could be captured simply as [StartTime] and [EndTime] with [HrsWorked] being the duration between the two times; [EndTime] – [StartTime].

Simple data set-up to capture values. Value Format is set as ‘Time’ in Cells A2 & B2

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.

Time duration as a decimal fraction of 24 hours

Adjusting to proper hours of a day

Use the PRODUCT ( ) Function as =PRODUCT(B2-A2,24) in Cell C2.

2 hours with a decimal fraction of 14 minutes

MROUND to desired multiple

Use the MROUND ( ) Function to return a number rounded to the nearest 6th 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).

[HrsWorked] formatted with tenths of an hour

The result

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.

8 thoughts on “Minutes to Decimal Hours (tenths)”

  1. glad i found this, HOWEVER, per your App C and a chart I have 7:41-3:30 should be 7.8 (11 minutes late). when i put 7:41-3:30 into your calculation, I end up with 7.9. Any suggestions?

    1. @Leslie, I checked the outputs and I believe the inputs of the function should output 7.9. See my results Decimal hours rounding check

  2. I really enjoyed this site. This is such a Great resource that you are providing and you give it away for free.

Leave a Reply to Master of Management Telkom University Cancel reply

Your email address will not be published. Required fields are marked *

css.php