formula to get the week range of that starts on sunday

[James Carlo Cruz]

Hi,
Here is my question :
I have an excel formula that will compute for the start of the week range (sunday) and end of the week (saturday).
=TEXT($D3-WEEKDAY($D3)+1,"Mmm dd")&"-"&TEXT(IF(MONTH($D3)<>MONTH($D3+6),DATE(YEAR($D3),MONTH($D3)+1,1)-1,$D3+MAX(IF(WEEKDAY($D3+{0,1,2,3,4,5,6})=7,{0,1,2,3,4,5,6},""))),"dd")

D3 = some dates

Need to change Dec 27 - 02 to Jan 01 - 02, would some one please tweak the formula?
Thanks!

 

[NARAYANK991]

Hi James ,

Will this work ?

=TEXT(IF(MONTH($D3-WEEKDAY($D3)+1)<>MONTH(D3),$D3,$D3-WEEKDAY($D3)+1),"Mmm dd")&"-"&TEXT(IF(MONTH($D3)<>MONTH($D3+6),DATE(YEAR($D3),MONTH($D3)+1,1)-1,$D3+MAX(IF(WEEKDAY($D3+{0,1,2,3,4,5,6})=7,{0,1,2,3,4,5,6},""))),"dd")

Narayan

 

[James Carlo Cruz]

Hi Narayan,

Good day!
Hope you are well.
Thanks for the help.
Will try to test this today, and hope this one works.

 

[James Carlo Cruz]

Hi Narayan,

I am sorry, the formula that I made and you tweak was not fit for my client.
They wanted a report that would always show :



D3 is some dates, in a month they always wanted to divide it to 5 week range
as shown in the column header. No matter what month it should be always in 5 week range

Is there a way that we can do this?
Thanks!

Regards,
JC

 

[azumi]

I cant's tweak your current formula, but hope this works, in D3 is your date, this on E3 and copied down

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