Creating a rolling sum function

[stugale]

Hi everyone,I am trying to create a spread sheet that adds up my staff's hours worked for a rolling 119 day period, sounds easy but I have to ignore any day that they have taken as leave but still include a 119 day period. For example if I had a member of staff who took 6 days off in that 119 day period I would need to add in the extra 6 days at the end, so I would need a 125 day period to be calculated.Is this possible? At the moment I have the rolling 119 day sum and a countif to tell me how many days leave (input as L) and then I manually add the extra cells to the formula. So my formula reads =SUM(C2:DQ2)+SUM(DR2:DW2) as my employee has taken 6 days leave. Many ThanksStu

 

[Legacy 421546]

Well, where is the leave count? Just add that to the sum

 

[stugale]

I have the leave count in row 3, so I have a rolling 119 day period of leave as well, but I'm not sure how to automatically adjust the formula to include the extra days. I have 45 members of staff so I'm looking for a way to automatically calculate the rolling number of hours worked rather than having the simple total and having to manually adjust the formula for every leave day.

 

[stugale]

Dummy example of what I'm trying to achieve -

[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Staff #[/TD]
[TD="class: xl64, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]01-Jul[/TD]
[TD="class: xl65, width: 64"]02-Jul[/TD]
[TD="class: xl65, width: 64"]03-Jul[/TD]
[TD="class: xl65, width: 64"]04-Jul[/TD]
[TD="class: xl65, width: 64"]05-Jul[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Jones[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]10[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]L[/TD]
[TD="class: xl64"]10[/TD]
[/TR]
</tbody>[/TABLE]

say I need to calculate a 4 day rolling sum of hours Jones would be 20 hours but because he had a leave day I need to do a 5 day rolling sum to account for his leave day. Therefore his total would be 30 hours. I need to expand this to cover 119 day period so not as easy as just looking and amending.

Thank You