Convert HHMM to decimal hours as per legal rules

English
,
#1

I found several and if I were smarter, I could modify them but sadly I’m a pilot. Thus my brain cells are good at other things but not this.

The way my company logs time internally is in HHMM, so when I extract it to put it in my logbook, it isn’t in tenths as it should be. eg my last three flights were 507 158 and 47. Before I import into my logbook, I need to convert it per the chart at this linktenths image

eg. with these three, it would be 5.2 2.0 and .8
Note, 158 is one hour and 58 minutes. Not 1.58. Thus 2.0 hours per the conversion chart.

Can anyone help me create the macro? Thanks!

#2

Why do you need a macro? Doesn’t the formula given,
=ROUNDUP(MROUND(PRODUCT(B2-A2,24),"0:06"),1)
work for you?
Did you format cells in A and B as Time?

Or did you want to take your already calculated total hours and convert them? For that I formatted the hour minute cell (cell F2) as [HH]:MM I then used CEILING function
=CEILING(F2*24,0.1)
because ROUNDUP rounded up to next hour and I didn’t want to find out why counts decimal places so
=ROUNDUP(F2*24,1) also works.

DecimalHourWorked.ods (10.9 KB)

#5

Ok, bare with me if you will. I only have one cell (one column) I am working with. eg 112. I flew 112 today, which is one hour and 12 minutes. When I try to format cell A1 : 112 to time I get either 0000 when I use the precreated Time 13:37 format option. When I create a user-defined, I get ####.

That said, if I just manually put 1:12 in, the roundup formula works. So steps in the right direction. I just don’t want to have to put colons in every row. Though after I run roundup on cell A1, it does change to show #### with the correct output in B1.

#6

Hallo

=INT(A1/100)+ROUNDUP(MOD(A1;100)/60;1)
#7

Somebody doing things in such an extremely stupid, misleading, ambiguous, and error-prone way should statutorily be barred from running a company.
But also there isn’t anything like “decimal hours”. What we are talking about is a duration given in the unit h with some decimal places.

You may also consider:

  • =VALUE(TEXT(A1;"00:00"))*24
    This formula will throw an error if the input has an illegal number of minutes.
    Rounding as needed, of course.

In recent versions of LibO the formula will work whether the input is text or number.

1 Like
#8

Holy balls, thank you! This did it all!! You are a god among humans, may you have the dessert of your choice for years to come without concern for weight or ingredients. My your dog poop only where they should. May your hair look perfect every day!

#3

Hallo

=ROUNDUP( (B2-A2)*24 ; 1)

both above and below Formula works the same way:

=CEILING( (B2-A2)*24 ; 0.1 )
#4

I was thinking the Excel formula looked over-complicated but I don’t know why it didn’t work for OP; I’m picking that his times are text not times.
I was under a bit of time pressure as there was a bird in the ceiling and my wife wanted it rescuing now. As it turned out it was just exploring.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.