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

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/

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

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.

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.

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/

"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

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

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

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