Forum Discussion
Hours Worked in Rolling Period
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).
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
That's very nice.
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))
Hi Philip and Sergei,
Thank you both very much for your solutions :)
Regards,
Leica
