Need formula for calculating time

[wjl408]

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

 

[Ron Rosenfeld]

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))

 

[joeu2004]

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 am not familiar with FAA rules for recording flight times. Based on your
description above, try:

=ROUND((TEXT(A4,"00\:00")-TEXT(A1,"00\:00")+(A1>A4))*24,1)

where A1 is the "start" time and A4 is the "shutdown" time. The interim
"off" and "on" times seem irrelevant, according to your example.

The factor +(A1>A4) accounts for the case where "start" and "shutdown" times
span midnight.

But that only works when "start" and "shutdown" times are no more than 24
hours apart.

Explanation....

For Excel, time is usually entered in the form hh:mm. So TEXT(A4,"00\:00")
converts the form hhmm to hh:mm. Caveat: Regional differences might apply.
See the Time Separator in the Regional and Language Options control panel
(in Win XP).

Excel time (hh:mm) is actually represented by a number composed of integer
days and time as a fraction (h/24 + m/1440 + s.sss/86400). So multiplying
the time difference by 24 converts to Excel time to a decimal number of
hours.

When A1>A4 is true, and A1 and A4 are within 24 hours, the time difference
is negative. In that case, we want to add 1 day (24 hours) to calculate the
elapsed time. Since (A1>A4) is TRUE, +(A1>A4) is treated as +1.

Rounding to 1 decimal place ensures that arithmetic result is "exactly"
accurate to 1 decimal places. This might not be necessary, depending on FAA
rules. It might be sufficient, perhaps even preferred, to remove the
ROUND(...,1) part, calculate the time difference exactly, and simply use the
format Number with 1 decimal place to cause the actual value to be
__displayed__ rounded to 1 decimal place.