Originally Posted by
wjl408
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 off 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 Off 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
Try this:
Line 1 is labels, Columns A through F are labeled:
DATE / OUT / OFF / ON / IN / TOTAL
(I used those labels because 2 "OFF" times is confusing)
Line 2 begins your entry of dates and times.
Columns B through E (the times) are formatted as CUSTOM, 0000 You enter 3 or 4 digits, it's displayed as 4 digits (123 displayed as 0123, 2230 displayed as 2230).
Column F is formatted as Number, 1 Decimal Place.
Again, Column B is the OUT time (or START to you), and Column E is the IN time (or the 2nd OFF to you).
Here's the formula for 2F -- the "Total time" displayed in tenths:
=((TIME(TRUNC(E2/100),MOD(E2,100),0))-(TIME(TRUNC(B2/100),MOD(B2,100),0))+((TIME(TRUNC(E2/100),MOD(E2,100),0))<(TIME(TRUNC(B2/100),MOD(B2,100),0))))*24
(Drag the formula down to fill the rest of the column.)
The formula converts the numbers you've entered to times, then calculates the difference between the times. The first part will handle times that occur on the same day. The second "half" handles times that begin on one day and continue on the next.
Now, I don't know if the minutes will convert to the tenths that you desire. I recall using a table of some sort to convert minutes to tenths. Here is how this formula will break it down:
MINUTES TENTHS
0-2 = .0
3-8 = .1
9-14 = .2
15-20 = .3
21-26 = .4
27-32 = .5
33-38 = .6
39-44 = .7
45-50 = .8
51-56 = .9
57-60 = 1.0
If that breakdown does not correspond to the conversion scheme you desire, we'll have to delve into some IF/THENs.
Hope that helps.
.