Excel formula for logbook

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/

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

In article m,
Wizard of Draws wrote:
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.

When you copy formula cells in Excel the default is to treat the other
cells referenced as relative, so that if you make 'B1' reference 'A1'
and then copy it to 'B2' it will reference 'A2'. To stop that you can
either name the cell or use $A$1 (the $ for each of the row and column
nails them down).

I suggested renaming because it even works across sheets (in case you
wanted the summary on a separate sheet) and the syntax for referring
to values in other sheets is ugly.

--
Ben Jackson

http://www.ben.com/

On 8/29/04 4:49 AM, in article nMgYc.201395$8_6.135186@attbi_s04, "Ben
Jackson" wrote:

In article m,
Wizard of Draws wrote:
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.

When you copy formula cells in Excel the default is to treat the other
cells referenced as relative, so that if you make 'B1' reference 'A1'
and then copy it to 'B2' it will reference 'A2'. To stop that you can
either name the cell or use $A$1 (the $ for each of the row and column
nails them down).

Actually, I forgot I knew this from working with a freebie spreadsheet long
ago. But I'm not sure it had a rename function.


I suggested renaming because it even works across sheets (in case you
wanted the summary on a separate sheet) and the syntax for referring
to values in other sheets is ugly.

Elegant is always preferable.
I like the hide column feature too. Thanks.
--
Jeff 'The Wizard of Draws' Bucchino
Cartoons with a Touch of Magic�
http://www.wizardofdraws.com
http://www.cartoonclipart.com