Splitting data from a week that has two months

[tryingcake]

Hi! Thank you in advance if you can help me!

I have to pull weekly data for payment on classes taught. No biggie. You teach 50 classes that week, you get paid for 50 classes. Easy until the last week of the month if it involves 2 months (say the 3/31 is on Wednesday and 4/1 is on Thursday). Classes taught are paid on full weeks. Easy Peasy. BUT - Bonuses are paid on actually months. For instance, this month The last paycheck for April will cover classes taught April 26 through May 2 and bonuses on all of April (4/1-30)only. So I need to be able to extract that last week with three different figures.

I have to factor in three different situations.

Data needed in one cell: Total classes taught for that week - No biggie. Straight forward.

Data needed in another cell: Total classes taught through 3/31 (Sunday through Wednesday)

Data needed in final cell: Total classes taught 4/1-4/3.

This information is on all on a weekly excel calendar in a workbook with a summary sheet. Each workbook is one month. They each need their own sheet due to other data also on the sheets.

I want to be able to grab week 4's data and have it extract the information automatically and carry it over to the summary sheet. Since the numbers on the calendar float around from one day to another, I've been doing it manually for 2.5 years now. There has to be an easier way.

And since the months have different numbers of days, that poses a problem beyond my skill level. Is there a way to set this up that it reads a formula on the summary page and I do not have to refigure the formulas each month? I'm open to suggestions. Thanks!

 

[KRice]

Here is a slight revision that adds an extra column for the totals that occur in the month before and the month after the current month. I changed the month-year in A3 to one that matches up with the examples above...Jan-2020 has a Monday the 27th and a Friday the 31st for direct comparisons, if a full-month solution is of any help.

MrExcel20200424.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1										EOM=end of month
2										BOM=beginning of month
3	Jan-2020	<-Mon-Yr
4		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total Sun-Sat	1st & Last Wk BOM to Sat OR Sun to EOM	1st & Last Wk total prior month OR total next month
5	Week 1	12/29	12/30	12/31	1/1	1/2	1/3	1/4
6	classes	2	9	8	7	6	12	13	57	38	19
7	Week 2	1/5	1/6	1/7	1/8	1/9	1/10	1/11
8	classes	1	2	1	2	1	2	1	10
9	Week 3	1/12	1/13	1/14	1/15	1/16	1/17	1/18
10	classes	0	3	4	4	3	7	11	32
11	Week 4	1/19	1/20	1/21	1/22	1/23	1/24	1/25
12	classes	1	4	3	9	8	7	12	44
13	Week 5	1/26	1/27	1/28	1/29	1/30	1/31	2/1
14	classes	0	4	14	11	8	11	14	62	48	14
15	Week 6	2/2	2/3	2/4	2/5	2/6	2/7	2/8
16	classes	0	4	14	11	8	11	14
17
tryingcake

Cell Formulas
Range	Formula
B5	B5	=INT((EOMONTH($A$3,-1))/7)*7+1
C15:H15,C13:H13,C11:H11,C9:H9,C7:H7,C5:H5	C5	=B5+1
I6,I16,I14,I12,I10,I8	I6	=IF(AND(EOMONTH(B5,0)>EOMONTH($A$3,0)),"",SUM(B6:H6))
J6,J16,J14,J12,J10,J8	J6	=IF(EOMONTH(B5,0)<EOMONTH($A$3,0),SUMIF(B5:H5,">="&EOMONTH($A$3,-1)+1,B6:H6),IF(AND(EOMONTH(B5,0)=EOMONTH($A$3,0),EOMONTH(H5,0)>EOMONTH($A$3,0)),SUMIF(B5:H5,"<="&EOMONTH($A$3,0),B6:H6),""))
K6,K16,K14,K12,K10,K8	K6	=IF(J6="","",I6-J6)
B7,B15,B13,B11,B9	B7	=H5+1