Forcing time to round to single decimal (flight calculator)

[bmwflyboy]

I need to log flight time. I currently have it set so that I can enter a date and time 5/5/2020 and say 19:03 for take off and landing is 5/6/2020 at 04:23. Which results in 9.3 hours. 19:03 to 04:23 is 9 hours and 20 mins. I need it to say 9.4 (19-24 mins = 0.4 from chart below), not 9.3.

I need to force the times into this format:

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

This is slightly different that the way Excel calculates time. If I fly for 1 min, excel will see that as 0.01667 and rounds it to 0.0. But I need it to say 0.1 even for only 1 min of flight.

I hope this makes sense and that I have provided enough detail. I feel that I am almost there, I just need a little boost to get to the end. Example is attached. Thank you for any help!

[Bo_Ry]

Please try
=CEILING((B3+B4-B1-B2)*24,0.1)

[AliGW]

Welcome to the forum.

Is this what you are after?

=IF(ISBLANK(B4),"-",ROUND(((B3-B1)+(B4-B2)),2)*24)

[bmwflyboy]

Thank you. I did try it and it works well in the beginning. Once I get to 12 mins, it switches to 0.3 instead of staying at 0.2 though.

I changed the dates to 5/5/2020 for takeoff and landing and the times to 19:00 take off and started working up the chart for landing time 19:01, 19:02...etc. When I get to 19:12 it changes to 0.3, but should remain 0.2.

[MrShorty]

Excel has several rounding functions to handle a variety of rounding scenarios. In column E, you are using number formatting which uses the standard "round to the nearest" that we're all familiar with. It appears to me that you want to round up to the nearest 0.1 hour or 6 minutes. Excel can do this using either the ROUNDUP() function or the CEILING() function.
ROUNDUP(decimal hours,1)
CEILING(time serial number,TIME(0,6,0))

[Bob Phillips]

Please try
=CEILING((B3+B4-B1-B2)*24,0.1)

Nice and elegant, and it works for the example provided, but I am not sure it works in all cases, for instance it returns 9.4 for 09:18, which should be 9.3.

My much klunkier suggestion

=IF(B4="","-",INT((((B3+B4)-(B1+B2))*24*60)/60)+((INT(MOD(((B3+B4)-(B1+B2))*24*60,60)/6.1)+1)/10))

[bmwflyboy]

So far this klunky solution seems to work the best! I just tested everything for an hour of time, and then started expanding it over several hours. The only prob I found was trying to do a flight from 5/5/2020 19:00 to 5/6/2020 landing 19:00. This returns a result of 24.1 ha ha…

[bmwflyboy]

And I noticed one other thing, if I enter 19:00 for take off and 19:59 for landing on the same day, it returns a 1 hour. Should be 0.9. I am very grateful for the formulas and effort on this. Is there a way to get that last part correct? I don't understand the klunky formula so I am at your mercy. Thank you again.

[bmwflyboy]

I stand corrected. I was wrong on that last post. Sorry.

[bmwflyboy]

Thank you everyone I am marking this as SOLVED. Thanks Bob!

[Bo_Ry]

How about this

=ROUNDUP(MROUND(B3+B4-B1-B2,"0:1")*24,1)

[bmwflyboy]

Thank you Bo_Ry, that is a less klunky solution!