Week of Month Range Formula

bcurtis208

New Member
Joined
Apr 1, 2011
Messages
6
Hi Everyone,

I'm hoping I can get assistance with this formula I'm trying to figure out. I have in the spreadsheet I'm working on a date in R2 of 01/20/2014. I would like to have it show in JC2 that the date that is in R2 is within the Week of 01/19 - 01/25.

Also, if the value of R2 was for example was 02/27. I would like the formula in JC2 to show the week ending to a date that lands at the end of the month. So I'd like the formula in JC2 to show result as: 02/23 - 02/28, I wouldn't want that range to end in 03/01 instead of 02/28.

I hope that makes sense. I appreciate any assistance I can get. Thank you so much.
 
Last edited:
Maybe this:

Code:
=TEXT(MAX(A2-MOD(A2+6,7),A2-DAY(A2)+1),"mm/dd - ")&TEXT(MIN(A2+(6-MOD(A2+6,7)),EOMONTH(A2,0)),"mm/dd")

Markmzz
 
Upvote 0
Or :-
Code:
=TEXT(A2-WEEKDAY(A2,2),"mm/dd - ")&TEXT(MIN(EOMONTH(A2,0),A2+7-WEEKDAY(A2,2)),"mm/dd")

hth
 
Upvote 0
A small modification in my formula (A2 to R2):

Code:
In JC2

=TEXT(MAX(R2-MOD(R2+6,7),R2-DAY(R2)+1),"mm/dd - ")&TEXT(MIN(R2+(6-MOD(R2+6,7)),EOMONTH(R2,0)),"mm/dd")

Markmzz
 
Upvote 0
A small modification in my formula (A2 to R2):

Code:
In JC2

=TEXT(MAX(R2-MOD(R2+6,7),R2-DAY(R2)+1),"mm/dd - ")&TEXT(MIN(R2+(6-MOD(R2+6,7)),EOMONTH(R2,0)),"mm/dd")

Markmzz

Hi markmzz,

This is close to what I'm needing and is what I asked for. However, (R2) value of 02/01/2014 results in the formula to populate 02/01 - 02/01. I'm needing that to result in 02/01 0 02/09. Below is an example of the date ranges I'm needing to populate from a formula, it may not be possible but I appreciate the assistance you can provide.

Week of
1/1 - 1/12
1/13 - 1/19
1/20 - 1/26
1/27 - 1/31
2/1 - 2/9
2/10 - 2/16
2/17 - 2/23
2/24 - 2/28
 
Upvote 0
Hi markmzz,

This is close to what I'm needing and is what I asked for. However, (R2) value of 02/01/2014 results in the formula to populate 02/01 - 02/01. I'm needing that to result in 02/01 0 02/09. Below is an example of the date ranges I'm needing to populate from a formula, it may not be possible but I appreciate the assistance you can provide.

Week of
1/1 - 1/12
1/13 - 1/19
1/20 - 1/26
1/27 - 1/31
2/1 - 2/9
2/10 - 2/16
2/17 - 2/23
2/24 - 2/28

Hi Bcurtis208,

Maybe this:

Code:
=TEXT(MAX(R2-MOD(R2+5,7),R2-DAY(R2)+1),"mm/dd - ")&
TEXT(MIN(R2+(6-MOD(R2+5,7))+(MOD(MAX(R2-MOD(R2+5,7),R2-DAY(R2)+1),7)<>2)*7,EOMONTH(R2,0)),"mm/dd")

Markmzz
 
Upvote 0

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