Add up hours worked in a week.

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
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 below are 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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Employee Name [/TD]
[TD]Hours Worked[/TD]
[TD]Total Hours Per Week[/TD]
[/TR]
[TR]
[TD]6/23/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]6/24/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/25/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/26/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/27/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/28/2018[/TD]
[TD]John Brown[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/29/2018[/TD]
[TD]John Brown[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,

Check if this works for you.


Book1
ABCD
1DateEmployee NameHours WorkedTotal Hours Per Week
206/23/2018John Brown1060
306/24/2018John Brown100
406/25/2018John Brown100
506/26/2018John Brown100
606/27/2018John Brown100
706/28/2018John Brown0
806/29/2018John Brown100
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A2,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A2+0,16)=WEEKNUM($A2,16)))=1)*($C$2:$C$8))
D3=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A3,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A3+0,16)=WEEKNUM($A3,16)))=1)*($C$2:$C$8))
D4=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A4,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A4+0,16)=WEEKNUM($A4,16)))=1)*($C$2:$C$8))
D5=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A5,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A5+0,16)=WEEKNUM($A5,16)))=1)*($C$2:$C$8))
D6=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A6,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A6+0,16)=WEEKNUM($A6,16)))=1)*($C$2:$C$8))
D7=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A7,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A7+0,16)=WEEKNUM($A7,16)))=1)*($C$2:$C$8))
D8=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A8,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A8+0,16)=WEEKNUM($A8,16)))=1)*($C$2:$C$8))
 
Last edited:
Upvote 0
It worked for one employee. But I have about 40 and when I copied the formula down, it didn't work for the rest. The layout is actually a table with headers as shown above. So I need the formula to apply to the entire table.
 
Upvote 0
Hi,

With the sample in mind, this is without using a helper column, try this:

Book1
ABCD
1DateEmployeeHoursTotal
223-6-2018Joe Brown1060
324-6-2018Joe Brown100
425-6-2018Joe Brown100
526-6-2018Joe Brown100
627-6-2018Joe Brown100
728-6-2018Joe Brown0
829-6-2018Joe Brown100
923-6-2018Tom Jones1060
1024-6-2018Tom Jones100
1125-6-2018Tom Jones100
1226-6-2018Tom Jones100
1327-6-2018Tom Jones100
1428-6-2018Tom Jones0
1529-6-2018Tom Jones100
1623-6-2018Sally Smith1060
1724-6-2018Sally Smith100
1825-6-2018Sally Smith100
1926-6-2018Sally Smith100
2027-6-2018Sally Smith100
2128-6-2018Sally Smith0
2229-6-2018Sally Smith100
Sheet1
Cell Formulas
RangeFormula
D2=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))
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top