Rolling totals for a 365 day period

[SMF28374]

I have a project that requires that I have a spreadsheet to track participation per day over a rolling 365 day period. In essence, an individual cannot exceed 90 days total participation over a 365 day period.

I built a spread sheet with the individual month on a row and individual days in columns with a total number of days in a given month to the right of the last day (ie Jan 31, Sep 30, etc.).

As an example, a person's participation on March 3-7 of a given year would "go away" the following March 3-7.

I tried inserting an image of what I have, but i cannot get it to show properly.

Any help would be greatly appreciated.

Steve

 

[MARK858]

Possibly something like the below?

Excel Workbook
	A	B	C
1	numbers	Date	Rolling
2	2	01/01/2010	2
3	3	02/02/2010	5
4	4	03/03/2010	9
5	2	07/06/2010	11
6	3	10/11/2010	14
7	4	31/12/2010	18
8	6	01/01/2011	24
9	2	02/02/2011	24
10	1	03/03/2011	22
11	3	07/06/2011	21
12	5	10/11/2011	24
13	2	01/01/2012	19
14	3	17/01/2012	16
Sheet4

Then apply an if statement for whatever you want to do when the total > 90 (Strictly speaking the above rolls back a year, for 365 days then

=SUMIFS($A$2:$A$14,$B$2:$B$14,"<="&B2,$B$2:$B$14,">="&DATE(YEAR(B2),MONTH(B2),DAY(B2)-365))

)

 

[SMF28374]

Thanks for the response and I will give this a try. I think this is close, but to make sure, let me try and clarify my requirement. Assume that an individual participates for 5 days in Jan 2010 (1/9 thru 1/13). As the year moves along, the dates of participation keep accumulating in the rolling total. Futher assume that, for this example, the individual never reaches the 90 maximum. When 1/9/2011 rolls around, the 1/9/210 day goes away unless there is participation for that day.

For each succeeding day, the rolling total would go up, down, or stay the same based upon participation for any given day.

Graphically, I would hope to have a chart showing year "X" displayed horizontally with months on the vertical in the first column and each day of the month on the horizontal across the first row. I would then have a mark for each day of participation in each corresponding cell and totals for the month at the right of each individual month and the rolling total the next column over. I would then have the following year in the same format under year "X" and would give me a format to "plug in" anticipated participation to verify that the 90 days will not be exceeded.

Sorry for the long winded narrative, but I can't seem to figure out how to place a graphic in my postings for clarity.

Again thank you so much for the assistance and I will give you feed back once I give this a try.

Steve

 

[SMF28374]

OK. I used your suggestion and it works fine for the rolling totals at each month end.

In looking at this more closely, I think I need to approach this as a running 365 day period to attain my desired result. In using my example in the previous post, on 1/9/2011 the individual participates. I "look back" to the 1/9/2010 cell to see if a value is present and, in this case, I am replacing the 2010 day with the 2011 day and the running total would stay the same and not increment. Following this scenario, my daily running total would then increment, decrement or stay the same based on subsequent "look backs" as days progress and alert me when the 90 day limit is or would be reached based on participation.

Does this make sense???

Thanks again for the assistance.

Steve

 

[MARK858]

I think I need to approach this as a running 365 day period to attain my desired result

The formula for 365 is in the previous post

Strictly speaking the above rolls back a year, for 365 days then

=SUMIFS($A$2:$A$14,$B$2:$B$14,"<="&B2,$B$2:$B$14,">="&DATE(YEAR(B2),MONTH(B2),DAY(B2)-365))

and it does state that you need to add an If statement to do whatever action you want when if the 90 days is hit

Then apply an if statement for whatever you want to do when the total > 90

You just hadn't stated what you wanted to happen if the total hit 90.
For instance you could do something like...

Excel Workbook
	A	B	C
1	numbers	Date	Rolling
2	10	01/01/2010	10
3	20	02/02/2010	30
4	10	03/03/2010	40
5	20	07/06/2010	60
6	10	10/11/2010	70
7	20	31/12/2010	Over 90
8	10	01/01/2011	Over 90
9	20	02/02/2011	Over 90
10	10	03/03/2011	Over 90
11	20	07/06/2011	Over 90
12	10	10/11/2011	Over 90
13	20	01/01/2012	Over 90
14	10	02/06/2012	60
Sheet1

Please note that my dates are in UK format dd/mm/yyyy

 

[arthurbr]

Cross post https://www.excelkey.com/forum/viewtopic.php?f=2&t=6529