Rolling running total by date
 

I am creating a flying log book on my computer. What I am trying to do is to
keep a total flying hours for the previous rolling 28 calender days. Column
'A' is the date and column 'M' is the hours flown. Days that I dont fly, have
no entry but must be included in the calculations.
Any help would be appreciated. Thank you
--
AOU

Rolling running total by date
 

Starting in cell B28, this formula will drag down for subsequent days
and sum the desired range on a rolling basis:
=SUM(OFFSET(A28,-27,0,1,1):A28)

Rolling running total by date
 

Thanks Dave. Before I try this, will it also sum days that are not entered.
ie If one entry is, say the 1st jan 07 then the next entry 5th jan 07, will
it include 2nd through to 4th as part of the 28 day total as zero figures?
--
AOU


"Dave O" wrote:

Starting in cell B28, this formula will drag down for subsequent days
and sum the desired range on a rolling basis:
=SUM(OFFSET(A28,-27,0,1,1):A28)

Rolling running total by date
 

That formula assumes a continuous run of dates, such May 1 to May 31.
It considers days that do not have an entry of flight hours and
includes those zero or null values in the calculation. To test, set
up a series of 28 days all with one hour entered: your total should be
28. Delete some of the ones at random to signify non-flying days, and
your total will reduce accordingly.

Rolling running total by date
 

That formula assumes a continuous run of dates, such May 1 to May 31.
It considers days that do not have an entry of flight hours and
includes those zero or null values in the calculation. To test, set
up a series of 28 days all with one hour entered: your total should be
28. Delete some of the ones at random to signify non-flying days, and
your total will reduce accordingly.

Rolling running total by date
 

Sorry Dave,
It comes back 'Too few arguments' it high lights :A28 at the end of the
formula.

--
AOU


"Dave O" wrote:

That formula assumes a continuous run of dates, such May 1 to May 31.
It considers days that do not have an entry of flight hours and
includes those zero or null values in the calculation. To test, set
up a series of 28 days all with one hour entered: your total should be
28. Delete some of the ones at random to signify non-flying days, and
your total will reduce accordingly.

Rolling running total by date
 

Dave,
Iv worked it out by using the following formula
=SUMPRODUCT(($A$6:$A$2003=F20546)*($A$6:$A$2003<E 20543)*($M$6:$M$2003))
And searching the sum of the hours between two dates.
Thanks for your help, it got me onto the right path.
--
AOU


"AOU" wrote:

I am creating a flying log book on my computer. What I am trying to do is to
keep a total flying hours for the previous rolling 28 calender days. Column
'A' is the date and column 'M' is the hours flown. Days that I dont fly, have
no entry but must be included in the calculations.
Any help would be appreciated. Thank you
--
AOU

Rolling running total by date
 

I do have another question? sorry!!!
Iv linked a textbox with a cell which has a value in 'hh:mm'. I am not able
to format the textbox to display the same value as the cell (ie 'hh:mm'). Can
you help?
--
AOU


"AOU" wrote:

Dave,
Iv worked it out by using the following formula
=SUMPRODUCT(($A$6:$A$2003=F20546)*($A$6:$A$2003<E 20543)*($M$6:$M$2003))
And searching the sum of the hours between two dates.
Thanks for your help, it got me onto the right path.
--
AOU


"AOU" wrote:

I am creating a flying log book on my computer. What I am trying to do is to
keep a total flying hours for the previous rolling 28 calender days. Column
'A' is the date and column 'M' is the hours flown. Days that I dont fly, have
no entry but must be included in the calculations.
Any help would be appreciated. Thank you
--
AOU