Hours Worked in Rolling Period

[Leica Dromgool]

Hi, this is probably dead easy but I am struggling to figure out how to make a function that generates how many total hours someone has worked in a 7-day and 28-day "rolling" period.

i.e. On day 3 (it will total day 1, 2 and 3). On day 4 (it will total day 1, 2, 3 and 4) etc. 

On day 8 (it totals the hours worked between day 2 and day 8). 

[#2]

The easy way to do it would be to 'manually fix' the first few days. IE

in Cell E2 of your spreadsheet add this formula: =SUM($D$2:D2) then drag it down to E7

After that add this to E8 =SUM(E3:E8) and drag to the end. Adjust for the 28day column.

If you want a formula that will do it all in one, then this works:

7 Days:

=IF(COUNT($C$2:C2)<8,SUM($C$2:C2),SUM(OFFSET(C2,-6,0):C2))

28 Days:

=IF(COUNT($C$2:C2)<29,SUM($C$2:C2),SUM(OFFSET(C2,-27,0):C2))

[#3]

As variant that could be

=SUM(OFFSET(C2,0,0,-MIN(ROW()-ROW($C$1),7)))

and

=SUM(OFFSET(C2,0,0,-MIN(ROW()-ROW($C$1),28)))

Attached

[#4]

That's very nice.

[#5]

Hi Philip and Sergei, 

Thank you both very much for your solutions :) 

Regards,
Leica