How to round specific minute values to decimal hours in Excel?

Question

I'm trying to set up a spreadsheet for tracking flight time for firefighting aircraft. I'm trying to digitize our hand-written form to reduce the need for physical forms. The pilots will input a lift-off time and touchdown time to get the flight time. The time entry cells are formatted with "0:00" and the calculation formula I'm using is:

=TEXT(E16,"0\:00")-TEXT(D16,"0\:00")

This allows the pilot to enter "1647" instead of "16:47" (I stole this from another spreadsheet so there may be a better way.)

We bill by hours and tenths so I need a formula to convert the result to hours and tenths by set minute increments as follows:

• 1-6 = 0.1
• 7-12 = 0.2
• 13-18 = 0.3
• 19-24 = 0.4
• 25-30 = 0.5
• 31-36 = 0.6
• 37-42 = 0.7
• 43-48 = 0.8
• 49-54 = 0.9
• 55-60 = 1.0

2:34 should output 2.6

I will then need the daily or flight leg results to total at the bottom of the page.

Answer 1

use MROUND:

=MROUND(TEXT(E16,"0\:00")-TEXT(D16,"0\:00")+(2.9/(24*60)),6/(24*60))*24

Answer 2

With integer minutes in column A, in B1 enter:

=ROUNDUP(TIME(0,A1,0)*60*60/150,1)

Answer 3

After perusing several forums, I created a overly complex formula that does what I need. It isolates the minutes, references a table of the minutes (on a hidden tab) and converts it to tenths, then adds the hours back into the result. Not as clean as some of the other replies, but it gets the job done. Thanks for the replies!

=IFERROR(LOOKUP(VALUE(RIGHT(MINUTE(TEXT(E16,"0\:00")-TEXT(D16,"0\:00")),2)),ConversionLookup!$B$3:$B$13,ConversionLookup!$D$3:$D$13)+VALUE(LEFT(HOUR(TEXT(E16,"0\:00")-TEXT(D16,"0\:00")),2)),0)

Lookup Table: