Add up the number of hours worked in a week. - Cross-Posted to Mr. Excel

Hello all,

I am trying to find a formula to add up how many hours an employee worked in each work week. There is lots of data on my sheet, but attached is a sample of the columns that I believe will be pulled from. I'm fine with adding a helper column (shown in my example as the "total hours per week" column). Note, our work weeks are Friday to Saturday, so I need that factored in during the summing. It would also be nice if I can get the data to just appear once per name.

Let me know if none of that makes sense.

Cross-Posted on Mr. Excel

Could you please tell us where you have cross-posted, as per the forum rules? Thanks.

Thank you. saying so in the post is fine - there's no need in the title, but thank you for being thorough.

Note, our work weeks are Friday to Saturday

The upload data shows Thursdays off. Does the workweek start Friday and end Wednesday?

This is a guess.

In the attached find a helper column in E. It returns the week number based on a Friday work week start. Change the second argument if that needs to change. The rest of the formulas will follow the change.

Formula:

=WEEKNUM(Table1[@Date],15)

In G2 and filled down this returns the unique week numbers.

Formula:

=IFERROR(AGGREGATE(15,6,WEEKNUM(+$A$2:$A$22,15)/(FREQUENCY(WEEKNUM(+$A$2:$A$22,15),

WEEKNUM(+$A$2:$A$22,15))>0),ROWS($A$2:$A2)),"")

With the employee names across H1:J1 this in H2 filled down and across. It returns the hours per week per employee.

Formula:

=IF($G2="","",SUM(SUMIFS($C:$C,$B:$B,H$1,$E:$E,$G2)))

Hi,

similar to my answer posted on MrExcel:

Without helper column, try this:

Formula:

=SUMPRODUCT(--($B$2:$B$22=B2)*--(WEEKNUM($A$2:$A$22+0,16)=WEEKNUM(A2,16))*(SUMPRODUCT(--($B$2:$B2=B2)*(WEEKNUM($A$2:$A2+0,16)=WEEKNUM($A2,16)))=1)*($C$2:$C$22))