On Wed, 30 Oct 2013 19:21:59 +0000, wjl408 wrote:


I need some help on a excel program I am making for our duty and flight
times for the FAA. I need the calculation for time and then convert it
to tenths. Example Start Off On Off times as entered onto the flight
logs, if your start is 0800 off 0815 on 0915 shutdown 0922 your block to
block is 0122 which would be flight time according to the FAA and would
be 1.4 hrs. I need the formula to get the 0122 block to block time by
figuring the minutes between the Start and shutdown times and then I
need the formula to derive the 1.4 hrs. from the Block to Block time.
Any help here would be greatly appreciated. Thanks

If you are entering the values as numbers, with no separators, things can get messy. You would be much better off entering full dates and times, in a manner Excel can understand. This will take care of the problems that may arise when the shift overlaps midnight, as well as provide an arguably better record for the FAA.

To derive the hours, one could then merely:

=Round((End-Start)*24,1)

If you enter the numbers as numbers, then you could convert them to time, and then do the math, something like:

=ROUND((REPLACE(B1,LEN(B1)-1,0,":")-REPLACE(A1,LEN(A1)-1,0,":"))*24,1)

except that if the time span went over midnight, you would either have to add 24 to the End time, or compensate for it in the formula:

=IF((ROUND((REPLACE(B1,LEN(B1)-1,0,":")-REPLACE(A1,LEN(A1)-1,0,":"))*24,1))0,
ROUND((REPLACE(B1,LEN(B1)-1,0,":")-REPLACE(A1,LEN(A1)-1,0,":"))*24,1),
24+ROUND((REPLACE(B1,LEN(B1)-1,0,":")-REPLACE(A1,LEN(A1)-1,0,":"))*24,1))