How to split week between month?

S

Sochen

Board Regular
Joined
Dec 6, 2022
Messages
66
Office Version
  1. 2021
Platform
  1. Windows
Hi All,

I want to make the partial week split between month as below example. And you may see the highlighted color which split between month.
So is there any formula can split it like this?

Book3
ABC
1Month01-Aug-22
2
3Week No.Week StartWeek End
43101-Aug-2207-Aug-22
53208-Aug-2214-Aug-22
63315-Aug-2221-Aug-22
73422-Aug-2228-Aug-22
83529-Aug-2231-Aug-22
93501-Sep-2204-Sep-22
103605-Sep-2211-Sep-22
113712-Sep-2218-Sep-22
123819-Sep-2225-Sep-22
133926-Sep-2230-Sep-22
143901-Oct-2202-Oct-22
154003-Oct-2209-Oct-22
164110-Oct-2216-Oct-22
174217-Oct-2223-Oct-22
184324-Oct-2230-Oct-22
194431-Oct-2231-Oct-22
204401-Nov-2206-Nov-22
214507-Nov-2213-Nov-22
224614-Nov-2220-Nov-22
Sheet1
Cell Formulas
RangeFormula
B4B4=B1
A4:A22A4=ISOWEEKNUM(B4)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Upvote 0
Select Cell 4 and use this conditional formatting. Then copy the formatting to other cells.
Untested:
Excel Formula: 
=OR(MONTH(C4)<>MONTH(C3),MONTH(C4)<>MONTH(C5))
 
Upvote 0
Hi,
You can test for cell C4 following formula
Excel Formula: 
=IF(MONTH(B4+(7-WEEKDAY(B4,2)))=MONTH(B4),B4+(7-WEEKDAY(B4,2)),EOMONTH(B4,0))
and for cell B5
Excel Formula: 
=C4+1
lastly you can have your correct column A ...
 
Upvote 0
Solution
snjpverma said:
Select Cell 4 and use this conditional formatting. Then copy the formatting to other cells.
Untested:
Excel Formula: 
=OR(MONTH(C4)<>MONTH(C3),MONTH(C4)<>MONTH(C5))
Click to expand...
Hi Sir, it returned #VALUE!
 
Upvote 0
James006 said:
Hi,
You can test for cell C4 following formula
Excel Formula: 
=IF(MONTH(B4+(7-WEEKDAY(B4,2)))=MONTH(B4),B4+(7-WEEKDAY(B4,2)),EOMONTH(B4,0))
and for cell B5
Excel Formula: 
=C4+1
lastly you can have your correct column A ...
Click to expand...
Hello Sir,

This is exactly what I want.
Thank you so much!
 
Upvote 0
Let me see if I can define what you're looking for in Week Start and Week End.
Week Start should be a Monday and Week End should be a Sunday
UNLESS seven days from Week Start is in the next month, then Week End should be the last day of the Month
UNLESS seven days from the Week Start goes past Sunday (i.e. Week Start is a Wednesday which would put Week End on a Tuesday), then Week End should be the next Sunday.
I came to this conclusion by reformatting your sample to be like this:
Book1
GHI
3Week No.Week StartWeek End
452Thursday, June 22, 1905Sunday, August 7, 2022
51Monday, August 8, 2022Sunday, August 14, 2022
62Monday, August 15, 2022Sunday, August 21, 2022
73Monday, August 22, 2022Sunday, August 28, 2022
84Monday, August 29, 2022Wednesday, August 31, 2022
95Thursday, September 1, 2022Sunday, September 4, 2022
Sheet1
Cell Formulas
RangeFormula
H4H4=B1
G4:G9G4=ISOWEEKNUM(B4)

Anyway, this is what I came up with based on that assumption
Book1
ABC
1YEAR: 2022
2Week #Week StartWeek End
352Monday, December 27, 2021Friday, December 31, 2021
452Saturday, January 1, 2022Sunday, January 2, 2022
51Monday, January 3, 2022Sunday, January 9, 2022
62Monday, January 10, 2022Sunday, January 16, 2022
73Monday, January 17, 2022Sunday, January 23, 2022
84Monday, January 24, 2022Sunday, January 30, 2022
95Monday, January 31, 2022Monday, January 31, 2022
105Tuesday, February 1, 2022Sunday, February 6, 2022
116Monday, February 7, 2022Sunday, February 13, 2022
127Monday, February 14, 2022Sunday, February 20, 2022
138Monday, February 21, 2022Sunday, February 27, 2022
149Monday, February 28, 2022Monday, February 28, 2022
159Tuesday, March 1, 2022Sunday, March 6, 2022
1610Monday, March 7, 2022Sunday, March 13, 2022
1711Monday, March 14, 2022Sunday, March 20, 2022
1812Monday, March 21, 2022Sunday, March 27, 2022
1913Monday, March 28, 2022Thursday, March 31, 2022
2013Friday, April 1, 2022Sunday, April 3, 2022
Sheet1
Cell Formulas
RangeFormula
A3:A20A3=ISOWEEKNUM(B3)
B3B3=DATE(B1, 1, 1) - WEEKDAY(DATE(B1, 1, 1),2 ) +1
C3:C20C3=IFS(MONTH(B3)<>MONTH(B3+7),EOMONTH(B3,0),TRUE,B3+7-WEEKDAY(B3,2))
B4:B20B4=C3+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:C52Expression=MOD(MONTH($B3),2)=1textNO

This is set up to do an entire calendar year where the calendar starts on the first Monday in the first week of January.
Just goes to show how important formatting - or not formatting can be!
I see you got exactly what you want, but I put a lot of work into this, and I'd like to know if I was close!
 
Upvote 0
jdellasala said:
Let me see if I can define what you're looking for in Week Start and Week End.
Week Start should be a Monday and Week End should be a Sunday
UNLESS seven days from Week Start is in the next month, then Week End should be the last day of the Month
UNLESS seven days from the Week Start goes past Sunday (i.e. Week Start is a Wednesday which would put Week End on a Tuesday), then Week End should be the next Sunday.
I came to this conclusion by reformatting your sample to be like this:
Book1
GHI
3Week No.Week StartWeek End
452Thursday, June 22, 1905Sunday, August 7, 2022
51Monday, August 8, 2022Sunday, August 14, 2022
62Monday, August 15, 2022Sunday, August 21, 2022
73Monday, August 22, 2022Sunday, August 28, 2022
84Monday, August 29, 2022Wednesday, August 31, 2022
95Thursday, September 1, 2022Sunday, September 4, 2022
Sheet1
Cell Formulas
RangeFormula
H4H4=B1
G4:G9G4=ISOWEEKNUM(B4)

Anyway, this is what I came up with based on that assumption
Book1
ABC
1YEAR: 2022
2Week #Week StartWeek End
352Monday, December 27, 2021Friday, December 31, 2021
452Saturday, January 1, 2022Sunday, January 2, 2022
51Monday, January 3, 2022Sunday, January 9, 2022
62Monday, January 10, 2022Sunday, January 16, 2022
73Monday, January 17, 2022Sunday, January 23, 2022
84Monday, January 24, 2022Sunday, January 30, 2022
95Monday, January 31, 2022Monday, January 31, 2022
105Tuesday, February 1, 2022Sunday, February 6, 2022
116Monday, February 7, 2022Sunday, February 13, 2022
127Monday, February 14, 2022Sunday, February 20, 2022
138Monday, February 21, 2022Sunday, February 27, 2022
149Monday, February 28, 2022Monday, February 28, 2022
159Tuesday, March 1, 2022Sunday, March 6, 2022
1610Monday, March 7, 2022Sunday, March 13, 2022
1711Monday, March 14, 2022Sunday, March 20, 2022
1812Monday, March 21, 2022Sunday, March 27, 2022
1913Monday, March 28, 2022Thursday, March 31, 2022
2013Friday, April 1, 2022Sunday, April 3, 2022
Sheet1
Cell Formulas
RangeFormula
A3:A20A3=ISOWEEKNUM(B3)
B3B3=DATE(B1, 1, 1) - WEEKDAY(DATE(B1, 1, 1),2 ) +1
C3:C20C3=IFS(MONTH(B3)<>MONTH(B3+7),EOMONTH(B3,0),TRUE,B3+7-WEEKDAY(B3,2))
B4:B20B4=C3+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:C52Expression=MOD(MONTH($B3),2)=1textNO

This is set up to do an entire calendar year where the calendar starts on the first Monday in the first week of January.
Just goes to show how important formatting - or not formatting can be!
I see you got exactly what you want, but I put a lot of work into this, and I'd like to know if I was close!
Click to expand...
Hello Sir,

This is also solve my problem with clear explanation. I highly appreciate it.
Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
A row of dates split into either weeks or months (depending on which was selected) based off of a start and end date
Replies
3
Views
427
Fluff
Fluff
S
  • Question Question
Split Monthly Budget to Weekly
Replies
4
Views
642
Tom.Jones
T
S
  • Question Question
List of week's first day but split when one week reach the end of a month
Replies
5
Views
756
jorismoerings
J
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
T
  • Question Question
cycle through multiple sheets
Replies
6
Views
763
ttowncorp
T

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

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