Calculate flying times using 24hour clock and 0000 general format

[Cobra2140]

I am trying to calculate flight hours using 4 digit 24 hour clock.
For example; an aircraft takes off at 1400 and lands at 1530. It flew for 1
hour and 30 minutes. The flight time should reflect 1.5

I have had limited success using B1-A1/60 but this only works for the first
59 minutes (it also rounds up .5 and I need it to round down).

I know the format to calculate the difference between two times but this
only seems to work if the two times are formatted as a time. This is
unnaceptable in our reports as the time must be four uninterrupted digits
(e.g. 0200 for 2 o'clock, 1050 for 10:50AM, 2345 for 11:45PM etc.)

Any suggestions?

 

[Peo Sjoblom]

One way, 1530 in B1 and 1400 in A1

=24*TEXT(B1-A1,"00\:00")

note that if the flight time starts before midnight and ends after you would
need something like this


=24*(MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1))

would return 2.5 if start is 2300 and end is 130



--


Regards,


Peo Sjoblom

 

[Gary''s Student]

In cells A1 thru C1 enter:

1400
1530
=24*(TIME(LEFT(B1,2),RIGHT(B1,2),0)-TIME(LEFT(A1,2),RIGHT(A1,2),0))

and format C1 as General

 

[Ron Rosenfeld]

I am trying to calculate flight hours using 4 digit 24 hour clock.
For example; an aircraft takes off at 1400 and lands at 1530. It flew for 1
hour and 30 minutes. The flight time should reflect 1.5

I have had limited success using B1-A1/60 but this only works for the first
59 minutes (it also rounds up .5 and I need it to round down).

I know the format to calculate the difference between two times but this
only seems to work if the two times are formatted as a time. This is
unnaceptable in our reports as the time must be four uninterrupted digits
(e.g. 0200 for 2 o'clock, 1050 for 10:50AM, 2345 for 11:45PM etc.)

Any suggestions?

The easy way: enter the times with the colon; e.g. 02:00 or 23:45, but format
them as hhmm for your reports so they will appear correctly.


Alternatively, to use your data as entered, and have the result in decimal
hours, you could use this formula:

=INT(EndTime/100)+MOD(EndTime/100,1)/0.6
-INT(StartTime/100)-MOD(StartTime/100,1)/0.6

Of course, you will have a problem with this formula if you go "past midnight".
If that is a problem, you could use this variation so long as the flight time
will always be less than 24 hrs.

=(StartTime>EndTime)*24+INT(EndTime/100)+MOD(EndTime/100,1)/0.6
-INT(StartTime/100)-MOD(StartTime/100,1)/0.6
--ron