Sat to Sun Week Range in a month

[James Carlo Cruz]

Hi Guys,

I have another question regarding week range, here is my formula :

=IF($A2<>"",TEXT(IF(WEEKNUM($A2)-WEEKNUM($A2-DAY(A2)+1)+1=1,DATE(YEAR($A2),MONTH($A2),1),A2-MOD($A2-1,7)),"Mmm dd")&"-"&TEXT(IF(WEEKNUM($A2)-WEEKNUM($A2-DAY($A2)+1)+1=ROUNDUP(DAY(DATE(YEAR($A2),MONTH($A2)+1,))/7,0),EOMONTH($A2,0),A2+7-WEEKDAY($A2+2,3)),"dd"),"")

where A is some date.

when date falls on 5 the range it is providing wrong week range

 

[Jake Collins]

Dear James

I think the following formula works.

=IF($A2<>"",TEXT(IF(WEEKNUM($A2)-WEEKNUM($A2-DAY(A2)+1)+1=1,DATE(YEAR($A2),MONTH($A2),1),A2-MOD($A2-1,7)),"Mmm dd")&"-"&TEXT(IF(WEEKNUM($A2)-WEEKNUM($A2-DAY($A2)+1)+1=ROUNDUP(DAY(DATE(YEAR($A2),MONTH($A2)+1,))/7,0),EOMONTH($A2,0),A2+7-WEEKDAY($A2+1,2)),"dd"),"")

 

[vletm]

@James Carlo Cruz
Depends Your week; Mon-Sun/Sun-Sat and the 1st week of Year ...
Try to find Your some help from this sample.

 

[James Carlo Cruz]

Hi Jake,

Thanks for your response, will try this later.

 

[James Carlo Cruz]

Hi Jake,

I have tried it, I need Sat and Sun week range, in the file that you gave if date falls in the middle of 2 months it will get the sat of previous month.
Is it possible to have November 29 to Dec 01, since I am looking only at monthly sat to sun week range.

 

[vletm]

@James Carlo Cruz
This my sample uses "dd/mm/yyyy"-format.
If You use "mm/dd/yyyy"-format then
You have to use that format with every date!
Even Excel cannot know is number 12 day or month.
Here, every 12 are months.