EXCEL HELP! How to allocate days within date ranges into weeks of the month

[nimishaasir]

Hello dear family!

I have been breaking my head over this last few hours. Could do wonders with your help !!

Problem statement:

I have been Given a date range (start date and end date), and now I need to put how many days within those dates - (stay with me) fall under each consecutive week of the month.
I am absolutely struggling with this.

I tried =IF((AND(B5>=M$4, B5<=N$4)),DATEDIF(B5,N$4,"d")+1,"") - to get it to count the number of days from start date to end date of the week, and do that for every week for every date range.

But its a problem if I have to replicate this across rows, and theres a date range which skips a week and starts the next week. I apologise for this excel trifle of a problem.

But please help!

Do you think Array might work ? or VBA? I dont know how to do that..

I have attached a link to the sheet thru google drive (T_T) please helpppp

Loading Google Sheets

 

[Anthony47]

Try in E5:

=SUM(IF(ISOWEEKNUM(ROW(INDIRECT($B5&":"&$C5)))=--RIGHT(E$3,2),1,0))

Then copy to the right and down