Split week's dates by month?

charlesstricklin

Board Regular
Joined
May 6, 2013
Messages
98
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDE
1StartEnd Company A Company B Total
2March 11, 2024March 18, 2024$ 170.00$ 124.00$ 294.00
3March 4, 2024March 11, 2024$ 183.00$ 83.00$ 266.00
4February 26, 2024March 4, 2024$ 212.00$ 70.00$ 282.00
5February 19, 2024February 26, 2024$ 213.00$ 88.00$ 301.00
6February 12, 2024February 19, 2024$ 162.00$ 160.00$ 322.00
7February 5, 2024February 12, 2024$ 64.00$ 118.00$ 182.00
8January 29, 2024February 5, 2024$ 161.00$ 159.00$ 320.00
9January 22, 2024January 29, 2024$ 163.00$ 228.00$ 391.00
10January 15, 2024January 22, 2024$ 75.00$ 138.00$ 213.00
11January 8, 2024January 15, 2024$ 88.00$ 208.00$ 296.00
12January 1, 2024January 8, 2024$ 65.00$ 149.00$ 214.00
13December 25, 2023January 1, 2024$ 193.00$ 194.00$ 387.00
Sheet2
Cell Formulas
RangeFormula
E2:E13E2=SUM(C2:D2)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
And the week starting on Feb 26 should belong to Feb or March?
Btw, why your weeks includes 2 "mondays"?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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-20244-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?
 
Upvote 0
Solution
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-20244-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.
 
Upvote 0
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
ABCDEFGHI
1StartEnd Company A Company B Total Income Month MonthIncome By Month
2March 11, 2024March 18, 2024$ 170.00$ 124.00$ 294.00MarJan$ 1,434.00
3March 4, 2024March 11, 2024$ 183.00$ 83.00$ 266.00MarFeb$ 1,087.00
4February 26, 2024March 4, 2024$ 212.00$ 70.00$ 282.00FebMar$ 560.00
5February 19, 2024February 26, 2024$ 213.00$ 88.00$ 301.00FebApr$ -
6February 12, 2024February 19, 2024$ 162.00$ 160.00$ 322.00FebMay$ -
7February 5, 2024February 12, 2024$ 64.00$ 118.00$ 182.00FebJun$ -
8January 29, 2024February 5, 2024$ 161.00$ 159.00$ 320.00JanJul$ -
9January 22, 2024January 29, 2024$ 163.00$ 228.00$ 391.00JanAug$ -
10January 15, 2024January 22, 2024$ 75.00$ 138.00$ 213.00JanSep$ -
11January 8, 2024January 15, 2024$ 88.00$ 208.00$ 296.00JanOct$ -
12January 1, 2024January 8, 2024$ 65.00$ 149.00$ 214.00JanNov$ -
13December 25, 2023January 1, 2024$ 193.00$ 194.00$ 387.00DecDec$ 387.00
14
15$ 3,468.00$ 3,468.00
Sheet3
Cell Formulas
RangeFormula
E2:E13E2=SUM(C2:D2)
F2:F13F2=TEXT(A2,"mmm")
I2:I13I2=SUMIF($F$2:$F$13,H2,$E$2:$E$13)
E15,I15E15=SUM(E2:E13)
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top