Adding a range of numbers associated with various dates

[missin44]

I’m working on a pilot flight logbook; all is going well except for a
couple things. Two features I want to add has me stumped.

1. I have 2 columns, column A5 thru A10000 has the date that I flew,
column C5 thru C10000 has the number of hours I flew. I need a formula
the will calculate the number of hours flown in any given 365, 182, 90,
60 and 30 day period.

2. When printing I want to be able simply hit the print button and have
it print only the rows that have actually have data. In other words if
there are 100 entries, say A1 thru A100, it will automatically set the
print area to print only A1 thru A100.

Hope you guys can help.

Back to top

 

[Dave O]

Hi, missin44-
Here's an answer to question 1: I mocked up some data based on your
description, with dates in A5:A33 and hours in C5:C33. In B35:B39 I
entered the "in the last #days" numbers you specified: 365, 182, 90,
60, and 30. Then in cell C35 I entered this formula:
=SUMPRODUCT(--($A$5:$A$33>=TODAY()-B35),$C$5:$C$33)

This formula checks the date range in A33:A35 to see if that date is
within the range of today's date minus the value in B35 (or 365 days in
this formula). If the date falls within that range, it adds the
corresponding value in column C. The "--" in the formula is a double
unary operator, which I can explain in great detail if you'd like. You
can copy and paste this formula into C36:C39 so the formula references
the values in B. Rather than hardcode the formula, this allows you
flexibility and scaleability.

As for question 2, can I suggest an alternative to devoting 10,000 rows
and have formulas at the bottom, and print out a ton of blank sheets or
go to extremes to print only the poplulated rows. If you set up the
formula to cover a certain number of rows (say, your existing volume of
data), you can insert new rows as additional flight time occurs. The
formulas will scale themselves to accommodate the additional rows AS
LONG AS you insert them into the existing range. This may mean leaving
a few blank rows at the bottom, but will be a more efficient use of
resources.

Dave O

 

[missin44]

Hi, missin44-
Here's an answer to question 1: I mocked up some data based on your
description, with dates in A5:A33 and hours in C5:C33. In B35:B39 I
entered the "in the last #days" numbers you specified: 365, 182, 90,
60, and 30. Then in cell C35 I entered this formula:
=SUMPRODUCT(--($A$5:$A$33>=TODAY()-B35),$C$5:$C$33)

This formula checks the date range in A33:A35 to see if that date is
within the range of today's date minus the value in B35 (or 365 days
in
this formula). If the date falls within that range, it adds the
corresponding value in column C. The "--" in the formula is a double
unary operator, which I can explain in great detail if you'd like. You
can copy and paste this formula into C36:C39 so the formula references
the values in B. Rather than hardcode the formula, this allows you
flexibility and scaleability.

As for question 2, can I suggest an alternative to devoting 10,000
rows
and have formulas at the bottom, and print out a ton of blank sheets
or
go to extremes to print only the poplulated rows. If you set up the
formula to cover a certain number of rows (say, your existing volume
of
data), you can insert new rows as additional flight time occurs. The
formulas will scale themselves to accommodate the additional rows AS
LONG AS you insert them into the existing range. This may mean leaving
a few blank rows at the bottom, but will be a more efficient use of
resources.

Dave O

Perfect, Thanks! I am curious about the "--" though.

 

[Dave O]

ARGH! I was afraid you'd say that!

Kidding, of course. As you know, SUMPRODUCT mutilplies values in one
column by values in a corresponding row in a different column and sums
the result. The double unary operator causes Excel to evaluate values
in the specified column as 1 or 0, so any cell in that column that does
not meet the criteria (in your case, within a date range) constitutes
multiplying by zero for that value, resulting in zero- so that row does
not add to the total.