Notices
Hangar Talk For non-aviation-related discussion and aviation threads that don't belong elsewhere

Excel calculation

Old 10-30-2013, 07:38 AM
  #1  
Line Holder
Thread Starter
 
wjl408's Avatar
 
Joined APC: Oct 2009
Position: Director of Operations CE-750 Captain
Posts: 65
Default Excel calculation

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
wjl408 is offline  
Old 10-30-2013, 09:35 AM
  #2  
Gets Weekends Off
 
Joined APC: Dec 2008
Position: 777 Cap
Posts: 199
Default Excel

Originally Posted by wjl408 View Post
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

Excelbanter.com excellent source
flap is offline  
Old 10-30-2013, 10:17 AM
  #3  
Gets Weekends Off
 
Adlerdriver's Avatar
 
Joined APC: Jul 2007
Position: 767 Captain
Posts: 3,987
Default

Originally Posted by wjl408 View Post
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
Are you going to be dealing with only short flights that happen during a given 24 hour day or is it possible you'll have longer flights that transition between local/Zulu days? (i.e. out at 2330L and in at 0400 the next day)

Are you trying to display the times without the colon, just as 4 digits?
Adlerdriver is offline  
Old 10-30-2013, 10:57 AM
  #4  
Line Holder
Thread Starter
 
wjl408's Avatar
 
Joined APC: Oct 2009
Position: Director of Operations CE-750 Captain
Posts: 65
Default

No colin, just the 4 didgits and all our flights are same day FXE to the Bahamas and back.
wjl408 is offline  
Old 10-30-2013, 02:43 PM
  #5  
Gets Weekends Off
 
Adlerdriver's Avatar
 
Joined APC: Jul 2007
Position: 767 Captain
Posts: 3,987
Default

Originally Posted by wjl408 View Post
No colin, just the 4 didgits and all our flights are same day FXE to the Bahamas and back.
I have a partial solution for you. It is mind boggling that excel doesn't have this capability built in already.

Select the columns you'll be entering your OOOI times in and select "format cells".

In the box that opens, make sure the "number" tab is the one selected (it's the first one, so it should be).

Click on "custom" in the category section on the left and then enter this: 00\:00 in the blank space under "type" and click "okay".

That will allow you to enter 4 digit times without colon in the columns/cell you custom format that way.

You can do a simple subtraction formula to compute block time in a "Block Time" column by subtracting the cell value in your "in" column from your cell value in your "out" column. Assuming your out/off/on/in columns were A/B/C/D with the block time column in E, your formula in E2 would be =D2-A2. With your example of out at 0800 and in at 0922, I was able to get a displayed block time of 01:22.

I'm assuming you are somewhat familiar with Excel and that stuff is probably more detail than you need. If you need more info let me know.

The conversion from total block time of 01:22 to a decimal of 1.4 in your example is proving difficult. The custom formatting is not actually "seen" by excel as a time display of hours:minutes. So far, I have not been able to figure a way to build a formula that will change the 01:22 displayed to 1.4 hours. I'll let you know if I have any luck. I'm not a serious excel expert, so maybe someone else has some ideas.

Last edited by Adlerdriver; 10-30-2013 at 03:11 PM.
Adlerdriver is offline  
Old 10-30-2013, 03:37 PM
  #6  
Gets Weekends Off
 
Singlecoil's Avatar
 
Joined APC: Jan 2010
Position: Supine
Posts: 250
Default

Excel is probably not the best application for that calculation. You can make a database in Access for that, or if you don't like driving yourself up the wall, you can make a database in Filemaker for that. Filemaker 12 has a free ios app to let you run and access databases from iphones and ipads.
Singlecoil is offline  
Old 10-30-2013, 04:58 PM
  #7  
Gets Weekends Off
 
Joined APC: Aug 2013
Position: FO
Posts: 627
Default

((final off time)-(first start time))*24. Format as a number with 1 decimal place.

Make sure you format the input times in a time format. I use hh:mm.
Toonces is offline  
Old 10-30-2013, 05:30 PM
  #8  
Organizational Learning 
 
TonyC's Avatar
 
Joined APC: Nov 2005
Position: Directly behind the combiner
Posts: 4,948
Default

Originally Posted by Adlerdriver View Post

I have a partial solution for you. It is mind boggling that excel doesn't have this capability built in already.

Select the columns you'll be entering your OOOI times in and select "format cells".

In the box that opens, make sure the "number" tab is the one selected (it's the first one, so it should be).

Click on "custom" in the category section on the left and then enter this: 00\:00 in the blank space under "type" and click "okay".

That will allow you to enter 4 digit times without colon in the columns/cell you custom format that way.

You can do a simple subtraction formula to compute block time in a "Block Time" column by subtracting the cell value in your "in" column from your cell value in your "out" column. Assuming your out/off/on/in columns were A/B/C/D with the block time column in E, your formula in E2 would be =D2-A2. With your example of out at 0800 and in at 0922, I was able to get a displayed block time of 01:22.

Ehh, not exactly.

That technique allows you to type 800 and have it display as 08:00, but the value of the cell is 800.

When you subtract 800 from 922, you get 122, and it's displayed as 1:22 -- looks right, but ...

Use 1:55, 2:00, 3:05, and 3:20 as the times ... the values 155, 200, 305, and 320 display as desired, but when you subtract 155 from 320, you get 165, which displays as 1:65 ... oops.


======================================

To the OP:

Why do you need it in tenths? I haven't converted minutes to tenths in over 15 years.






.
TonyC is offline  
Old 10-30-2013, 06:14 PM
  #9  
Administrator
 
vagabond's Avatar
 
Joined APC: May 2006
Position: C-172
Posts: 8,024
Default

Originally Posted by wjl408 View Post
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.
Are you a lawyer!?! OMG.
vagabond is offline  
Old 10-30-2013, 06:31 PM
  #10  
Organizational Learning 
 
TonyC's Avatar
 
Joined APC: Nov 2005
Position: Directly behind the combiner
Posts: 4,948
Default

Originally Posted by wjl408 View Post

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.






.
TonyC is offline  
Related Topics
Thread
Thread Starter
Forum
Replies
Last Post
Hoof Hearted
Regional
3
09-03-2011 06:14 PM
FIREPILOT2
Fractional
8
08-15-2009 05:24 AM
usmc-sgt
Hangar Talk
2
07-27-2006 04:26 PM
Pilot41
Hangar Talk
3
07-27-2006 09:32 AM

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Thread Tools
Search this Thread
Your Privacy Choices