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

[KyleOliver]

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>

A	B	C	D	E	F	G	H
1	Week Day<strike></strike>	<strike></strike>Punch Date<strike></strike>	<strike></strike>Time In<strike></strike>	<strike></strike>Lunch Start<strike></strike>	<strike></strike>Lunch End<strike></strike>	<strike></strike>Time Out<strike></strike>	<strike></strike>Hours Worked<strike></strike>
2	Monday	01 Aug 2018	08:00	12:00	13:00	16:00	7:00
3	Tuesday	02 Aug 2018	08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
4	Wednesday	03 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
5	Thursday	04 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
6	Friday	05 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
7	Saturday	06 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
8	Sunday	07 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
9	Monday	08 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
10	Tuesday	09 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
11	Wednesday	10 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
12	Thursday	11 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
13	Friday	12 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
14	Saturday	13 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>
15	Sunday	14 Aug 2018​ <strike></strike>	<strike></strike>
16	Monday	15 Aug 2018​ <strike></strike>	<strike></strike> 08:00​ <strike></strike>	<strike></strike> 12:00​ <strike></strike>	<strike></strike> 13:00​ <strike></strike>	<strike></strike> 16:00​ <strike></strike>	<strike></strike> 7:00​ <strike></strike>

<tbody>
</tbody>

<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]

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]

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]

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]

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	1	Week Day	Punch Date	Time In	Lunch Start	Lunch End	Time Out	Hours Worked
2	2	Wednesday	Wed 01-Aug-18	8:00	12:00	13:00	16:00	7:00
3	3	Thursday	Thu 02-Aug-18	8:00	12:00	13:00	16:00	7:00
4	4	Friday	Fri 03-Aug-18	8:00	12:00	13:00	16:00	7:00
5	5	Saturday	Sat 04-Aug-18	8:00	12:00	13:00	16:00	7:00
6	6	Sunday	Sun 05-Aug-18	8:00	12:00	13:00	16:00	7:00
7	7	Monday	Mon 06-Aug-18	8:00	12:00	13:00	16:00	7:00
8	8	Tuesday	Tue 07-Aug-18	8:00	12:00	13:00	16:00	7:00
9	9	Wednesday	Wed 08-Aug-18	8:00	12:00	13:00	16:00	7:00
10	10	Thursday	Thu 09-Aug-18	8:00	12:00	13:00	16:00	7:00
11	11	Friday	Fri 10-Aug-18	8:00	12:00	13:00	16:00	7:00
12	12	Saturday	Sat 11-Aug-18	8:00	12:00	13:00	16:00	7:00
13	13	Sunday	Sun 12-Aug-18	8:00	12:00	13:00	16:00	7:00
14	14	Monday	Mon 13-Aug-18	8:00	12:00	13:00	16:00	7:00
15	15	Tuesday	Tue 14-Aug-18
16	16	Wednesday	Wed 15-Aug-18	8:00	12:00	13:00	16:00	7:00
17
18
19	Sunday	14:00:00
20	Sunday	14:00:00
21		14:00:00
22		14:00:00
23		14:00:00
24	7	14:00:00
2a

Cell Formulas
Range	Formula
B19	=SUMPRODUCT(--(B2:B16="Sunday"),(H2:H16))
B20	=SUMPRODUCT(--(B2:B16=A20),(H2:H16))
B21	=SUMPRODUCT((WEEKDAY(C2:C16,2)=7)*(MONTH(C2:C16)=8)*(H2:H16))
B22	=SUMPRODUCT(--(WEEKDAY(C2:C16,2)=7),--(MONTH(C2:C16)=8),(H2:H16))
B23	=SUMPRODUCT(--(WEEKDAY(C2:C16,2)=7),(H2:H16))
B24	=SUMPRODUCT(--(WEEKDAY(C3:C18,2)=A24),(H3:H18))

 

[Dave Patton]

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

Your formula edited

Excel 2010
	B	C	D	E	F
25	14:00:00				Sun 05-Aug-18
26	14:00:00
2a

Cell Formulas
Range	Formula
B25	=SUMPRODUCT(--(TEXT(C2:C16,"ddd")="Sun"),(H2:H16))
B26	=SUMPRODUCT(--(TEXT(C2:C16,"ddd")=TEXT(F25,"ddd")),(H2:H16))

 

[KyleOliver]

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

Thanks Dave,

Your formula is working great. Much appreciated!

Regards,
Kyle