Excel formula for logbook

"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 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