Split week's dates by month?

[charlesstricklin]

I don't know if this is even possible but you guys have pulled off the incredible before so I thought I'd ask,

Imagine I have a sheet where a start and end date of each week worked are in columns A & B, two different companies in columns C & D, and a sum of each row of data in column E.

I'd like to be able to separate data by month. Since both column A & B include a date range that frequently goes over between the end of a month and the beginning, is that going to make it impossible to split?

Income.xlsx
	A	B	C	D	E
1	Start	End	Company A	Company B	Total
2	March 11, 2024	March 18, 2024	$ 170.00	$ 124.00	$ 294.00
3	March 4, 2024	March 11, 2024	$ 183.00	$ 83.00	$ 266.00
4	February 26, 2024	March 4, 2024	$ 212.00	$ 70.00	$ 282.00
5	February 19, 2024	February 26, 2024	$ 213.00	$ 88.00	$ 301.00
6	February 12, 2024	February 19, 2024	$ 162.00	$ 160.00	$ 322.00
7	February 5, 2024	February 12, 2024	$ 64.00	$ 118.00	$ 182.00
8	January 29, 2024	February 5, 2024	$ 161.00	$ 159.00	$ 320.00
9	January 22, 2024	January 29, 2024	$ 163.00	$ 228.00	$ 391.00
10	January 15, 2024	January 22, 2024	$ 75.00	$ 138.00	$ 213.00
11	January 8, 2024	January 15, 2024	$ 88.00	$ 208.00	$ 296.00
12	January 1, 2024	January 8, 2024	$ 65.00	$ 149.00	$ 214.00
13	December 25, 2023	January 1, 2024	$ 193.00	$ 194.00	$ 387.00
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=SUM(C2:D2)

 

[Anthony47]

And the week starting on Feb 26 should belong to Feb or March?
Btw, why your weeks includes 2 "mondays"?

 

[dreid1011]

It's possible. Though you will likely have to decide how you want the overlapping data to behave. Do you want to include it in the month it starts, or the month it ends? And How do you want it to look? Can you show us an example of the expected results?

 

[rlv01]

Initially your problem is not an excel expertise question, it is a domain expertise question. You have to have some approved method or formula for allocating which portion goes to which month when the start and end dates straddle a month boundary. One you define the method, then implementing it becomes an excel question.

 

[charlesstricklin]

FYI These are the weekly statements as they appear on both Uber & Lyft's websites. Both companies consider 4:00 AM on Monday through the following Monday at 4:00 AM as a complete week. I'm just looking for a better way of gathering and viewing income.

 

[rlv01]

FYI These are the weekly statements as they appear on both Uber & Lyft's websites. Both companies consider 4:00 AM on Monday through the following Monday at 4:00 AM as a complete week. I'm just looking for a better way of gathering and viewing income.

But the needed action right now is yours. You must explain how you want to split income between two months. Let's take this example:

26-Feb-2024

4-Mar-2024

$ 212.00

$ 70.00

$ 282.00

How much of the $282 should be allocated to Feb, and how much to March? And how did you arrive at those amounts/percentages?

 

[charlesstricklin]

But the needed action right now is yours. You must explain how you want to split income between two months. Let's take this example:

26-Feb-2024

4-Mar-2024

$ 212.00

$ 70.00

$ 282.00

How much of the $282 should be allocated to Feb, and how much to March? And how did you arrive at those amounts/percentages?

Got it. It looks like I'm forced to download and pull my data from the daily reports. I'm sorry for wasting you guys' time with such a bonehead question.

 

[rlv01]

Got it. It looks like I'm forced to download and pull my data from the daily reports. I'm sorry for wasting you guys' time with such a bonehead question.

Not boneheaded at all. It just requires some thinking about how you want to handle it. A simple way (but not necessarily the best way) would be to decide that you will always allocate the full 'straddle' amount to the the first month, so all $282 in my example would go to Feb.

An example:

Book1
	A	B	C	D	E	F	G	H	I
1	Start	End	Company A	Company B	Total	Income Month		Month	Income By Month
2	March 11, 2024	March 18, 2024	$ 170.00	$ 124.00	$ 294.00	Mar		Jan	$ 1,434.00
3	March 4, 2024	March 11, 2024	$ 183.00	$ 83.00	$ 266.00	Mar		Feb	$ 1,087.00
4	February 26, 2024	March 4, 2024	$ 212.00	$ 70.00	$ 282.00	Feb		Mar	$ 560.00
5	February 19, 2024	February 26, 2024	$ 213.00	$ 88.00	$ 301.00	Feb		Apr	$ -
6	February 12, 2024	February 19, 2024	$ 162.00	$ 160.00	$ 322.00	Feb		May	$ -
7	February 5, 2024	February 12, 2024	$ 64.00	$ 118.00	$ 182.00	Feb		Jun	$ -
8	January 29, 2024	February 5, 2024	$ 161.00	$ 159.00	$ 320.00	Jan		Jul	$ -
9	January 22, 2024	January 29, 2024	$ 163.00	$ 228.00	$ 391.00	Jan		Aug	$ -
10	January 15, 2024	January 22, 2024	$ 75.00	$ 138.00	$ 213.00	Jan		Sep	$ -
11	January 8, 2024	January 15, 2024	$ 88.00	$ 208.00	$ 296.00	Jan		Oct	$ -
12	January 1, 2024	January 8, 2024	$ 65.00	$ 149.00	$ 214.00	Jan		Nov	$ -
13	December 25, 2023	January 1, 2024	$ 193.00	$ 194.00	$ 387.00	Dec		Dec	$ 387.00
14
15					$ 3,468.00				$ 3,468.00
Sheet3

Cell Formulas
Range	Formula
E2:E13	E2	=SUM(C2:D2)
F2:F13	F2	=TEXT(A2,"mmm")
I2:I13	I2	=SUMIF($F$2:$F$13,H2,$E$2:$E$13)
E15,I15	E15	=SUM(E2:E13)