Re: How to calulate hours worked on Sundays in a specific month

KyleOliver · Sep 2, 2018

Re: How to calulate hours worked on Sundays in a specific month

Hi,

I would like to know how to add all hours worked on the sundays in a month.
An employee might not work every Sunday in a month. Some employees will work one Sunday, others might work 2 or more and some might not work any Sundays in a month.

My table looks as follows;

<strike></strike><strike></strike>[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Week Day<strike></strike>[/TD]
[TD]<strike></strike>Punch Date<strike></strike>[/TD]
[TD]<strike></strike>Time In<strike></strike>[/TD]
[TD]<strike></strike>Lunch Start<strike></strike>[/TD]
[TD]<strike></strike>Lunch End<strike></strike>[/TD]
[TD]<strike></strike>Time Out<strike></strike>[/TD]
[TD]<strike></strike>Hours Worked<strike></strike>[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Monday[/TD]
[TD]01 Aug 2018[/TD]
[TD]08:00[/TD]
[TD]12:00[/TD]
[TD]13:00[/TD]
[TD]16:00[/TD]
[TD]7:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tuesday[/TD]
[TD]02 Aug 2018[/TD]
[TD]

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Wednesday[/TD]
[TD]

03 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Thursday[/TD]
[TD]

04 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Friday[/TD]
[TD]

05 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Saturday[/TD]
[TD]

06 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sunday[/TD]
[TD]

07 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Monday[/TD]
[TD]

08 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tuesday[/TD]
[TD]

09 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Wednesday[/TD]
[TD]

10 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Thursday[/TD]
[TD]

11 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Friday[/TD]
[TD]

12 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Saturday[/TD]
[TD]

13 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Sunday[/TD]
[TD]

14 Aug 2018

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Monday[/TD]
[TD]

15 Aug 2018

08:00

12:00

13:00

16:00

7:00

<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
How do I calculate just the hours worked on a Sunday?
Do I use a SUMIF / SUMIFS / SUMPRODUCT / MATCH / INDEX?

I have no idea, please someone help?

Regards,
Kyle

PCL · Sep 2, 2018

Re: How to calulate hours worked on Sundays in a specific month

Try

=SUMPRODUCT((B2:B16="Sunday")*(H2:H16))

Do you know that instead of day name you could use date and make the calculation per month

PCL · Sep 2, 2018

Re: How to calulate hours worked on Sundays in a specific month

Let's assume that in column C there is some Excel date like dd/mm/yyyy
Next formula wil give the hours sum =SUMPRODUCT((WEEKDAY(C2:C16;2)=7)*(MONTH(C2:C16)=8)*(H2:H16))

in column B put = column C with a custom format like dddd it gives the weekday day

KyleOliver · Sep 3, 2018

Re: How to calulate hours worked on Sundays in a specific month

Hi PCL,

Thank-you for your response.

I used the following formula which worked;

=SUMPRODUCT((TEXT(B16:B46,"ddd")=TEXT(F13,"ddd"))*H16:H46)

Thanks for your willingness to assist a novice like me.

Regards,
Kyle

Dave Patton · Sep 3, 2018

Re: How to calulate hours worked on Sundays in a specific month

Your example shows incorrect weekdays.
Ensure the formula separators are correct for your region.

Excel 2010

A

B

C

D

E

F

G

H

1

Week Day

Punch Date

Time In

Lunch Start

Lunch End

Time Out

Hours Worked

2

Wednesday

Wed 01-Aug-18

8:00

12:00

13:00

16:00

7:00

3

Thursday

Thu 02-Aug-18

8:00

12:00

13:00

16:00

7:00

4

Friday

Fri 03-Aug-18

8:00

12:00

13:00

16:00

7:00

5

Saturday

Sat 04-Aug-18

8:00

12:00

13:00

16:00

7:00

6

Sunday

Sun 05-Aug-18

8:00

12:00

13:00

16:00

7:00

7

Monday

Mon 06-Aug-18

8:00

12:00

13:00

16:00

7:00

8

Tuesday

Tue 07-Aug-18

8:00

12:00

13:00

16:00

7:00

9

Wednesday

Wed 08-Aug-18

8:00

12:00

13:00

16:00

7:00

10

Thursday

Thu 09-Aug-18

8:00

12:00

13:00

16:00

7:00

11

Friday

Fri 10-Aug-18

8:00

12:00

13:00

16:00

7:00

12

Saturday

Sat 11-Aug-18

8:00

12:00

13:00

16:00

7:00

13

Sunday

Sun 12-Aug-18

8:00

12:00

13:00

16:00

7:00

14

Monday

Mon 13-Aug-18

8:00

12:00

13:00

16:00

7:00

15

Tuesday

Tue 14-Aug-18

16

Wednesday

Wed 15-Aug-18

8:00

12:00

13:00

16:00

7:00

17

18

19

Sunday