• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

week range per month

J

James Carlo Cruz

Member
Hi All,
Good day! Hope you are well, I found this site helpful specially with the help of Narayan and Azumi.

I wanted to ask if there are formula that could be working for my concern?

No matter what month, it should be divided into five weeks.

upload_2016-3-5_22-24-59.png

I have created a formula : =TEXT(IF(MONTH($D2-WEEKDAY($D2)+1)<>MONTH(D2),$D2,$D2-WEEKDAY($D2)+1),"Mmm dd")&"-"&TEXT(IF(MONTH($D2)<>MONTH($D2+6),DATE(YEAR($D2),MONTH($D2)+1,1)-1,$D2+MAX(IF(WEEKDAY($D2+{0,1,2,3,4,5,6})=7,{0,1,2,3,4,5,6},""))),"dd")

D2 = some date
The Formula I have created would show Jan 01 - 02 as a week range, but my client wanted me to show it as Jan 1 - 7, every month should be divided into 5 week range.

upload_2016-3-5_22-28-38.png
 
Hi ,

But surely in all but leap years , February would have only 4 weeks according to this split up :

Feb 1 - 7 , 8 - 14 , 15 - 21 , 22 - 28

Narayan
 
Hi Narayan,

Template was very helpful on my end, I could use this.
This is what I am looking for.

I would like to ask, so that i can troubleshoot this on my own.
Formula was always referring to a blank date, any reason behind it?

Regards,
JC
 
Hi ,

This is merely to take care of February when it has only 28 days , since in this one case , the 5th week will not be present.

If your requirement is that the entire year needs to be presented in this format , let me know.

Narayan
 
You must log in or register to reply here.
Back
Top