Week of Month Range Formula

[bcurtis208]

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.

 

[markmzz]

Maybe this:

=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

 

[ukmikeb]

Or :-

=TEXT(A2-WEEKDAY(A2,2),"mm/dd - ")&TEXT(MIN(EOMONTH(A2,0),A2+7-WEEKDAY(A2,2)),"mm/dd")

hth

 

[markmzz]

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

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

 

[bcurtis208]

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

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

 

[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

Hi Bcurtis208,

Maybe this:

=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