Excel calculation
10-31-2013, 02:56 AM
#11
Line Holder
Thread Starter
Joined APC: Oct 2009
Position: Director of Operations CE-750 Captain
Posts: 65
The conversion to tenths is for logged flight time, I've always logged hobbs time as flight time (wheels off, wheels on) but our POI going over our duty logs wants the FAA definition of flight time used which is Block to Block. And the reason for making a spreadsheet of the duty logs is because they want to know when your at home dispatching a flight how do you know if the pilot assigned is legal and within their duty and rest times.
10-31-2013, 02:58 AM
#12
Line Holder
Thread Starter
Joined APC: Oct 2009
Position: Director of Operations CE-750 Captain
Posts: 65
Originally Posted by TonyC
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.
.
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.
.
10-31-2013, 07:01 AM
#13
Line Holder
Thread Starter
Joined APC: Oct 2009
Position: Director of Operations CE-750 Captain
Posts: 65
Tony C you are the man, I don't think I would have ever figured out that formula. But I used it and finished my spreadsheet and all works Great. Thank You very much.
10-31-2013, 08:35 AM
#14
Organizational Learning
Joined APC: Nov 2005
Position: Directly behind the combiner
Posts: 4,948
Originally Posted by wjl408
Tony C you are the man, I don't think I would have ever figured out that formula. But I used it and finished my spreadsheet and all works Great. Thank You very much.
.
08-07-2020, 12:25 PM
#16
New Hire
Joined APC: Aug 2020
Posts: 1
Originally Posted by TonyC
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.
.
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.
.
TonyC, if you’re still active, would you be able to help me modify this formula with if/then for different time break downs?
07-28-2022, 04:47 PM
#17
New Hire
Joined APC: Jul 2022
Posts: 3
"0.0" versus "0.1"
Originally Posted by TonyC
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.
.
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.
.
Tony,
Thank you for the formula you've provided. It has helped many of us tremendously. Unfortunately, there seems to be a slight calculation error.
EXAMPLE:
OUT: 0600
IN: 0701
The example provided above reflects a 1-hour and 1-minute flight. According to Excel, the result is "1.0"
Correct me if I'm wrong, but shouldn't the answer be "1.1"? Hear me out...
Let's say, for the sake of argument, a pilot starts logging time at 12:00. In the span of two minutes, something happens where the aircraft has to be shut down. The pilot would create two entries:
START: 12:00
END: 12:02
TOTAL: 2 minutes
Wouldn't that constitute a "0.1" in their logbook? (I'd say so.)
(Back to the initial example I provided.)
Since the time advanced 1-minute past the "1.0" marker, shouldn't the Excel formula reflect that additional minute as "1.1 in the total-time column"?
I see you provided a "MINUTES TENTHS" table, but flight times should be rounded up (ROUNDUP) to the next "0.1".
SUGGESTION:
I did a bit of configuring myself.
=((LEFT(TEXT(B2-A2,"0000"),2))*1)+ROUNDUP(IF((RIGHT(B2,2))<(RIGHT( A2,2)),(60-(RIGHT(A2,2))+(RIGHT(B2,2))),((RIGHT(B2,2))-(RIGHT(A2,2))))/60,1)
A1: Start Time /// B1: Stop Time /// C1: Total
FORMAT: "General"
A2 and B2
FORMAT: "More Number Formats"... "Custom"... Type: 0000
C3
FORMAT: "Number"
MINUTES TENTHS
(New formula)
01-06 = 0.1
07-12 = 0.2
13-18 = 0.3
19-24 = 0.4
25-30 = 0.5
31-36 = 0.6
37-42 = 0.7
43-48 = 0.8
49-54 = 0.9
55-60 = 1.0
If anyone has any feedback, please let me know.
FYI - This was my first post on this forum.
08-10-2022, 10:14 AM
#18
New Hire
Joined APC: Jul 2022
Posts: 3
Originally Posted by rmontoya
SUGGESTION:
I did a bit of configuring myself.
=((LEFT(TEXT(B2-A2,"0000"),2))*1)+ROUNDUP(IF((RIGHT(B2,2))<(RIGHT( A2,2)),(60-(RIGHT(A2,2))+(RIGHT(B2,2))),((RIGHT(B2,2))-(RIGHT(A2,2))))/60,1)
The previous Excel formula I provided could not account for times starting before 2400 and ending afterward.
Also, I added an "IFERROR" to keep the blocks clean when no data is present.
Here's an update:
=IFERROR((IF(A2>B2,RIGHT(TEXT(LEFT(TEXT((A2-2400)*-1,"0000"),2)+LEFT(TEXT((B2),"0000"),2),"0000"),2)+ ROUNDUP(IF((RIGHT(B2,2))<(RIGHT(A2,2)),(60-(RIGHT(A2,2))+(RIGHT(B2,2))),((RIGHT(B2,2))-(RIGHT(A2,2))))/60,1),((LEFT(TEXT(B2-A2,"0000"),2))*1)+ROUNDUP(IF((RIGHT(B2,2))<(RIGHT( A2,2)),(60-(RIGHT(A2,2))+(RIGHT(B2,2))),((RIGHT(B2,2))-(RIGHT(A2,2))))/60,1))),"")
NOTE: Message for Excel experts. The formula is a bit clunky, but it is functional. If anyone would like to streamline it, please share it with the group. Thank you.
08-15-2022, 02:24 AM
#19
Gets Weekends Off
Joined APC: Feb 2006
Position: B-737NG preferably in first class with a glass of champagne and caviar
Posts: 5,902
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
https://www.faa.gov/documentlibrary/...n_8900.368.pdf
Bottom line… if your operation can’t afford a proper canned record keeping program, then they should not be an air carrier operation.
Thread
Thread Starter
Forum
Replies
Last Post
