If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel formula for logbook
I have my logbook duplicated in Excel, but now that I've added my instrument
rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... Unfortunately I'm just barely competent with the program and don't have a clue about how to go about this. The best I can do is create a formula that returns a date 6 month previous. Where to go from there and how to tie the date column to the approaches column is beyond me. Any one of you folks have this formula setup already, or have a few hints that could point me in the right direction? -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com |
#2
|
|||
|
|||
In article m,
Wizard of Draws wrote: I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... I'd make a cell somewhere with the cutoff date for approaches, which is: =DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1) (Excel seems to tolerate negative months and compensates correctly) You can name that cell CUTOFF by putting the name in the box to the left of the formula entry (the box will normally have the name of the cell as A1 or whatever). You can put this cell on another sheet in the same workbook. Then add a column like =IF(A1 = CUTOFF, B1, 0) where A is the column with the date of the flight and B1 is the number of approaches. Then the sum of this column is the number of approaches after the cutoff. Then you can hide that column so it doesn't clutter the display. You could do something similar for landing currency. Figuring the cutoff is easier since it's a straight 90 days (and in Excel dates are numbers where 1.0 = 1 day). It's just =TODAY()-90. -- Ben Jackson http://www.ben.com/ |
#3
|
|||
|
|||
On 8/26/04 8:25 PM, in article jcvXc.239753$eM2.90248@attbi_s51, "Ben
Jackson" wrote: In article m, Wizard of Draws wrote: I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... I'd make a cell somewhere with the cutoff date for approaches, which is: =DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1) (Excel seems to tolerate negative months and compensates correctly) You can name that cell CUTOFF by putting the name in the box to the left of the formula entry (the box will normally have the name of the cell as A1 or whatever). You can put this cell on another sheet in the same workbook. Then add a column like =IF(A1 = CUTOFF, B1, 0) where A is the column with the date of the flight and B1 is the number of approaches. Then the sum of this column is the number of approaches after the cutoff. Then you can hide that column so it doesn't clutter the display. You could do something similar for landing currency. Figuring the cutoff is easier since it's a straight 90 days (and in Excel dates are numbers where 1.0 = 1 day). It's just =TODAY()-90. Thanks Ben, but I'm not quite sure that's what I'm looking for. Part of my confusion could be how I'm interpreting the calculation of "last 6 months" for currency, so please bear with me. There's also a good possibility that I'm misunderstanding your formula workings. As I said, I'm not proficient with the program. I assume from my reading that as long as I have 6 approaches within the last 6 months, I'm IFR legal. I'm also assuming that that time frame keeps coming forward as I do approaches. In other words, if I go out and do 3 approaches this weekend, and then another 3 next weekend, the 6 month timeframe will advance ahead 1 week. I don't interpret the 6 month timeframe as a static calendar window starting from your checkride date. So... I'm looking for the Excel logbook to tell me if I'm IFR current every time I open it, by looking back 6 months from *that day* and counting the number of approaches. I appreciate your patience. -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com |
#4
|
|||
|
|||
On 8/26/04 6:39 PM, Wizard of Draws wrote the following:
I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... Unfortunately I'm just barely competent with the program and don't have a clue about how to go about this. The best I can do is create a formula that returns a date 6 month previous. Where to go from there and how to tie the date column to the approaches column is beyond me. Any one of you folks have this formula setup already, or have a few hints that could point me in the right direction? I started down that path, then switched to SafeLog from faatest.com. Very low ROI on my time fooling with Excel even though I am a computer geek by trade. Safelog imports and exports Excel so you are not locked in and it does all the currency stuff, even filling in an 8710 for you when you are going for a check ride. It's kind of quirky in the user interface but it's effective and cheap. The CFII author is kind of a nut (in a pleasant sort of way), and he welcomes suggestions, bug reports, etc. and is very responsive. I have no commercial interest, etc. -- just a satisfied user. |
#5
|
|||
|
|||
www.logshare.com also works very well and its free. It does a nice job of
tracking currency. You can download to excel anytime to keep a local backup. Can't upload however, so first time around I had to hand enter the first 70 or so entries in my logbook. -Brenor "Geo. Anderson" wrote in message ... On 8/26/04 6:39 PM, Wizard of Draws wrote the following: I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... Unfortunately I'm just barely competent with the program and don't have a clue about how to go about this. The best I can do is create a formula that returns a date 6 month previous. Where to go from there and how to tie the date column to the approaches column is beyond me. Any one of you folks have this formula setup already, or have a few hints that could point me in the right direction? I started down that path, then switched to SafeLog from faatest.com. Very low ROI on my time fooling with Excel even though I am a computer geek by trade. Safelog imports and exports Excel so you are not locked in and it does all the currency stuff, even filling in an 8710 for you when you are going for a check ride. It's kind of quirky in the user interface but it's effective and cheap. The CFII author is kind of a nut (in a pleasant sort of way), and he welcomes suggestions, bug reports, etc. and is very responsive. I have no commercial interest, etc. -- just a satisfied user. |
#6
|
|||
|
|||
In article m,
Wizard of Draws wrote: On 8/26/04 8:25 PM, in article jcvXc.239753$eM2.90248@attbi_s51, "Ben Jackson" wrote: I'd make a cell somewhere with the cutoff date for approaches, which is: =DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1) Then add a column like =IF(A1 = CUTOFF, B1, 0) where A is the column with the date of the flight and B1 is the number of approaches. Then the sum of this column is the number of approaches after the cutoff. Thanks Ben, but I'm not quite sure that's what I'm looking for. Part of my confusion could be how I'm interpreting the calculation of "last 6 months" I assume from my reading that as long as I have 6 approaches within the last 6 months, I'm IFR legal. ...no person may act as pilot in command under IFR or in weather conditions less than the minimums prescribed for VFR, unless within the preceding 6 calendar months The key phrase is 'calendar months'. This month is not a 'preceding' month, so you always get this month free. Then you get to count approaches the 6 preceding months. That's why the formula above goes back to the 1st of the month, and then back 6 months. So... I'm looking for the Excel logbook to tell me if I'm IFR current every time I open it, by looking back 6 months from *that day* and counting the number of approaches. Exactly. The 'looking back' part is the column with the IF stmt. If you had a logbook like: Date Approaches IF... 8/04/2003 4 0 1/15/2004 1 0 5/19/2004 3 3 6/21/2004 1 1 9 total, 4 count toward currency today Once you generate the IF column you can hide it and just use the total to do something else. -- Ben Jackson http://www.ben.com/ |
#7
|
|||
|
|||
"Wizard of Draws" wrote in message news:BD53ECF6.1DF92%jeffbREMOVE@REMOVEwizardofdraw s.com... I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... Unfortunately I'm just barely competent with the program and don't have a clue about how to go about this. The best I can do is create a formula that returns a date 6 month previous. Where to go from there and how to tie the date column to the approaches column is beyond me. Any one of you folks have this formula setup already, or have a few hints that could point me in the right direction? -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com Let's say you have Col A=date, Col B = number of Inst approaches, and Col C = number of landings if you wanted D100 to be the number of applicable instrument approaches you would define D100 =countWithinDuration( $A$1:$A100, $B$1:$B100, 6, "M") if you wanted E100 to be the number of applicable landings you would define E100 =countWithinDuration( $A$1:$A100, $C$1:$C100, 90, "D") if you wanted F100 to be the date that represents "how far back" you have to look for the needed instrument approaches: F100=countBackHowFar( 6, $A$1:$A100, $B$1:B100) and if you wanted G100 to be the number of days remaining on your instrument currency G100= max(0, datediff(A100, date(year(F100),month(F100)+7,0), "D")) or for say your landings days remaining of currency, combining it all together, you would have: H100=max(0, datediff( A100, countBackHowFar( 3, $A$1:$A100,$B$1:$B100) + 90, "D")) and here's the two functions, paste them into your macros window: Function countWithinDuration(dates As Range, events As Range, farBack As Integer, durationCode As String) Dim rcnt As Integer rcnt = dates.Rows.Count Dim asof As Date asof = dates(rcnt, 1) Dim tot As Integer tot = 0 For i = rcnt To 1 Step -1 If DateDiff(durationCode, dates(i, 1), asof) = farBack Then tot = tot + events(i, 1) End If Next i countWithinDuration = tot End Function Function countBackHowFar(numEventsNeeded As Integer, dates As Range, events As Range) Dim tot As Integer tot = 0 Dim rowcount As Integer rowcount = dates.Rows.Count countBackHowFarArrays = 0 For i = rowcount To 1 Step -1 tot = tot + events(i, 1) If tot = numEventsNeeded Then countBackHowFar = dates(i, 1) Exit Function End If Next i End Function (Hope there's no typos-- I switched to OpenOffice in midstream so I had to re-convert them back to the Excel version just now in my head. I can dig out the polished version and send it to you if you want). I did it just for fun one evening. It makes for nice plots of a quantative "how current are you" sort...(I posted a bitmap of one over on alt.binaries.pictures.aviation) Cheers, John Clonts Temple, Texas N7NZ |
#8
|
|||
|
|||
On 8/27/04 12:36 AM, in article kTyXc.240963$eM2.179070@attbi_s51, "Ben
Jackson" wrote: In article m, Wizard of Draws wrote: On 8/26/04 8:25 PM, in article jcvXc.239753$eM2.90248@attbi_s51, "Ben Jackson" wrote: I'd make a cell somewhere with the cutoff date for approaches, which is: =DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1) Then add a column like =IF(A1 = CUTOFF, B1, 0) where A is the column with the date of the flight and B1 is the number of approaches. Then the sum of this column is the number of approaches after the cutoff. Thanks Ben, but I'm not quite sure that's what I'm looking for. Part of my confusion could be how I'm interpreting the calculation of "last 6 months" I assume from my reading that as long as I have 6 approaches within the last 6 months, I'm IFR legal. ...no person may act as pilot in command under IFR or in weather conditions less than the minimums prescribed for VFR, unless within the preceding 6 calendar months The key phrase is 'calendar months'. This month is not a 'preceding' month, so you always get this month free. Then you get to count approaches the 6 preceding months. That's why the formula above goes back to the 1st of the month, and then back 6 months. So... I'm looking for the Excel logbook to tell me if I'm IFR current every time I open it, by looking back 6 months from *that day* and counting the number of approaches. Exactly. The 'looking back' part is the column with the IF stmt. If you had a logbook like: Date Approaches IF... 8/04/2003 4 0 1/15/2004 1 0 5/19/2004 3 3 6/21/2004 1 1 9 total, 4 count toward currency today Once you generate the IF column you can hide it and just use the total to do something else. Ben, you are a steely-eyed missile man. That makes sense to me now. I wondered why I was coming up with a date that was the first of the month. My initial date fixing formula was =now()-182. Even then, I was stuck at that point trying to draw a relationship between dates and approaches. Thanks again for your patience. -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com |
#9
|
|||
|
|||
On 8/27/04 7:15 PM, in article , "John
Clonts" wrote: "Wizard of Draws" wrote in message news:BD53ECF6.1DF92%jeffbREMOVE@REMOVEwizardofdraw s.com... I have my logbook duplicated in Excel, but now that I've added my instrument rating, it might be nice to have Excel keep track of my currency on approaches. Maybe flag a cell as red or green depending upon my status... Unfortunately I'm just barely competent with the program and don't have a clue about how to go about this. The best I can do is create a formula that returns a date 6 month previous. Where to go from there and how to tie the date column to the approaches column is beyond me. Any one of you folks have this formula setup already, or have a few hints that could point me in the right direction? -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com Let's say you have Col A=date, Col B = number of Inst approaches, and Col C = number of landings if you wanted D100 to be the number of applicable instrument approaches you would define D100 =countWithinDuration( $A$1:$A100, $B$1:$B100, 6, "M") if you wanted E100 to be the number of applicable landings you would define E100 =countWithinDuration( $A$1:$A100, $C$1:$C100, 90, "D") if you wanted F100 to be the date that represents "how far back" you have to look for the needed instrument approaches: F100=countBackHowFar( 6, $A$1:$A100, $B$1:B100) and if you wanted G100 to be the number of days remaining on your instrument currency G100= max(0, datediff(A100, date(year(F100),month(F100)+7,0), "D")) or for say your landings days remaining of currency, combining it all together, you would have: H100=max(0, datediff( A100, countBackHowFar( 3, $A$1:$A100,$B$1:$B100) + 90, "D")) and here's the two functions, paste them into your macros window: Function countWithinDuration(dates As Range, events As Range, farBack As Integer, durationCode As String) Dim rcnt As Integer rcnt = dates.Rows.Count Dim asof As Date asof = dates(rcnt, 1) Dim tot As Integer tot = 0 For i = rcnt To 1 Step -1 If DateDiff(durationCode, dates(i, 1), asof) = farBack Then tot = tot + events(i, 1) End If Next i countWithinDuration = tot End Function Function countBackHowFar(numEventsNeeded As Integer, dates As Range, events As Range) Dim tot As Integer tot = 0 Dim rowcount As Integer rowcount = dates.Rows.Count countBackHowFarArrays = 0 For i = rowcount To 1 Step -1 tot = tot + events(i, 1) If tot = numEventsNeeded Then countBackHowFar = dates(i, 1) Exit Function End If Next i End Function (Hope there's no typos-- I switched to OpenOffice in midstream so I had to re-convert them back to the Excel version just now in my head. I can dig out the polished version and send it to you if you want). I did it just for fun one evening. It makes for nice plots of a quantative "how current are you" sort...(I posted a bitmap of one over on alt.binaries.pictures.aviation) Cheers, John Clonts Temple, Texas N7NZ John, I appreciate your efforts, but I think Ben's solution is more along the lines that I can handle at this point. I will play with your macro however, and try to dissect it for the learning experience. Thanks again. -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com |
#10
|
|||
|
|||
On 8/27/04 9:10 PM, in article
, "Wizard of Draws" wrote: On 8/27/04 12:36 AM, in article kTyXc.240963$eM2.179070@attbi_s51, "Ben Jackson" wrote: In article m, Wizard of Draws wrote: On 8/26/04 8:25 PM, in article jcvXc.239753$eM2.90248@attbi_s51, "Ben Jackson" wrote: I'd make a cell somewhere with the cutoff date for approaches, which is: =DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1) Then add a column like =IF(A1 = CUTOFF, B1, 0) where A is the column with the date of the flight and B1 is the number of approaches. Then the sum of this column is the number of approaches after the cutoff. Thanks Ben, but I'm not quite sure that's what I'm looking for. Part of my confusion could be how I'm interpreting the calculation of "last 6 months" I assume from my reading that as long as I have 6 approaches within the last 6 months, I'm IFR legal. ...no person may act as pilot in command under IFR or in weather conditions less than the minimums prescribed for VFR, unless within the preceding 6 calendar months The key phrase is 'calendar months'. This month is not a 'preceding' month, so you always get this month free. Then you get to count approaches the 6 preceding months. That's why the formula above goes back to the 1st of the month, and then back 6 months. So... I'm looking for the Excel logbook to tell me if I'm IFR current every time I open it, by looking back 6 months from *that day* and counting the number of approaches. Exactly. The 'looking back' part is the column with the IF stmt. If you had a logbook like: Date Approaches IF... 8/04/2003 4 0 1/15/2004 1 0 5/19/2004 3 3 6/21/2004 1 1 9 total, 4 count toward currency today Once you generate the IF column you can hide it and just use the total to do something else. Ben, you are a steely-eyed missile man. That makes sense to me now. I wondered why I was coming up with a date that was the first of the month. My initial date fixing formula was =now()-182. Even then, I was stuck at that point trying to draw a relationship between dates and approaches. Thanks again for your patience. I also wanted to comment that, for my version of Excel at least, it was required for me to rename the cell that contained the cutoff formula. For some reason, just referencing the default cell name made the formula not work. I was also able to get the cell containing the sum of approaches after the cutoff date, to flag itself red or green depending upon my currency status using a conditional formula. -- Jeff 'The Wizard of Draws' Bucchino Cartoons with a Touch of Magic www.wizardofdraws.com www.cartoonclipart.com |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lasher Renegade Formula V racer Information | Curtis Scholl | Home Built | 1 | February 27th 20 05:12 PM |
Using Excel or Access to keep track of students/records? | BoDEAN | Instrument Flight Rules | 5 | October 2nd 03 05:07 AM |
Millionaire at 31... on the Internet. Listen to how he's doing it. | ower | Home Built | 0 | August 2nd 03 10:23 AM |