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]

ADVERTISEMENT

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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Week Day</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Punch Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Time In</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Lunch Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Lunch End</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Time Out</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Hours Worked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wednesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wed 01-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Thu 02-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Friday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 03-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Saturday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sat 04-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sunday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sun 05-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Monday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 06-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Tue 07-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wednesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wed 08-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Thu 09-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Friday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 10-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Saturday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sat 11-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sunday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sun 12-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Monday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 13-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Tue 14-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wednesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Wed 15-Aug-18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">13:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">16:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">7:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #222222;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #222222;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #222222;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #222222;background-color: #FAFAFA;;"></td><td style="text-align: center;border-top: 1px solid black;color: #222222;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Sunday</td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Sunday</td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">7</td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B19</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B2:B16="Sunday"</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B20</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B2:B16=A20</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B21</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">WEEKDAY(<font color="Green">C2:C16,2</font>)=7</font>)*(<font color="Red">MONTH(<font color="Green">C2:C16</font>)=8</font>)*(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B22</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">C2:C16,2</font>)=7</font>),--(<font color="Red">MONTH(<font color="Green">C2:C16</font>)=8</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">C2:C16,2</font>)=7</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B24</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">WEEKDAY(<font color="Green">C3:C18,2</font>)=A24</font>),(<font color="Red">H3:H18</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Dave Patton]

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

Your formula edited

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sun 05-Aug-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;color: #333333;;">14:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B25</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">C2:C16,"ddd"</font>)="Sun"</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B26</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">C2:C16,"ddd"</font>)=TEXT(<font color="Green">F25,"ddd"</font>)</font>),(<font color="Red">H2:H16</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[KyleOliver]

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

Thanks Dave,

Your formula is working great. Much appreciated!

Regards,
Kyle