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!

 

[GraH]

Hi, can you tell us if your calendar are real dates formatted as either "Monday" to "Sunday" and days of month from 1 until 30/31?
If so then it should be possible to evaluate each date against the end of month = EOMONTH(Date,0). Regardless of there being 28/29 (leap year) days in Feb or whether there are 30/31 days in a month.

 

[tryingcake]

Thank you, Grah. I see how that will locate the date in question. I do not see how this will do the math for all data before and after that date. I googled trying to find the answer on my own but was not successful.

Using the example in the first post, when everything is said and done, I should have three cells with the following answers:

Cell 1/ Total classes for 27-1: 62
Cell 2/ Total class 27-31: 48
Cell 3/ Total classes 1-2: 14

From there I know how to calculate the financial info I need from those numbers.

As fas as the dates go, I can make them however they need to be. Right now they are actual dates starting with the first calendar I created 2.5 years ago. But I can take the time to make any changes necessary if this will solve my issue going forward.

Thank you for responding and helping. I would love an answer to this dilemma.

 

[GraH]

It might work like this

Kenshin.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2		27	28	29	30	31	01	02		Total in week	Total Current Month	Total Next Month
3		4	14	11	8	11	14	0		62	48	14
4
Sheet2

Cell Formulas
Range	Formula
B1:H1	B1	=B2
J3	J3	=SUM(B3:H3)
K3	K3	=SUMIFS(B3:H3,B2:H2,"<="&EOMONTH(B2,0))
L3	L3	=SUMIFS(B3:H3,B2:H2,">"&EOMONTH(B2,0))

Monday-Sunday: dates are formatted as "dddd"
27-02: dates are fromatted as "dd"

 

[KRice]

Here is another option to consider. Enter a month/yr in A3 and the calendar should build itself in the grid. Full-week totals are shown in column I, regardless of whether they span a month boundary. Column J contains the partial weeks...those that either run from the 1st of the month to Saturday (the end of that week), or run from the end of the month back to Sunday (the beginning of the last week in that month). So you'll find the "Cell 2" and "Cell 3" entries in column J. The Cell 3 entry (meaning the potential partial week at the beginning of the month) will always be found in J6. The Cell 2 entry (meaning the potential partial week at the end of the month) will be found in either J14 or J116, depending on how that particular month falls on the calendar grid. For example, a 31-day month beginning on Saturday will end on Monday of week 6, so its "Cell 2" entry would be found on J16. It would be easiest to simply add J14 and J16 to yield the Cell 2 value...the formula is set to 0 out whichever week is not relevant.
BOM = beginning of month
EOM = end of month

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3	Mar-2021	<-Mon-Yr
4		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total Sun-Sat	1st & Last Wk BOM to Sat OR Sun to EOM
5	Week 1	2/28	3/1	3/2	3/3	3/4	3/5	3/6
6	classes	2	9	8	7	6	12	13	57	55
7	Week 2	3/7	3/8	3/9	3/10	3/11	3/12	3/13
8	classes	1	2	1	2	1	2	1	10
9	Week 3	3/14	3/15	3/16	3/17	3/18	3/19	3/20
10	classes	0	3	4	4	3	7	11	32
11	Week 4	3/21	3/22	3/23	3/24	3/25	3/26	3/27
12	classes	1	4	3	9	8	7	12	44
13	Week 5	3/28	3/29	3/30	3/31	4/1	4/2	4/3
14	classes	0	4	14	11	8	11	14	62	29
15	Week 6	4/4	4/5	4/6	4/7	4/8	4/9	4/10
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),""))
B7,B15,B13,B11,B9	B7	=H5+1

 

[tryingcake]

Grah - I tried yours first and it worked! Woot!! Thank you!!

Thank you to everyone who offered help. I was at a total loss. I knew there had to be a solution. Thank you!! Thank you!! Thank you!!

 

[GraH]

With pleasure. Thx for feeding back it worked out.

 

[KRice]

@tryingcake, I'm glad you have something that works, and I'm happy to help. By the way, I'm not sure if you're aware of it, but in both of the approaches above, you can click on the icon I've circled in purple to copy the sample to your clipboard, and then paste it directly into your Excel worksheet...so there is no tedious setup involved. Usually I do the pasting into whichever cell shows in the upper left of the posted sample (A1 in both cases above).

 

[GraH]

I didn't know Thanks for letting us know, KRice.

 

[Fluff]

Along with what @KRice about copying the data, if you only want the values & not the formulae, then you can Alt Click the copy logo.
Especially useful if an OP has posted data with formula pointing to other sheets.