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 (...
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