Formulas to help split a month into weeks (mon-sun)

Hello,

I am working on a monthly revenue tracker for our sales department and trying to automate a few calculations. In the attached workbook, I have a cell for them to input the first day of the month, C9, which will always be the 1st. I have have cell that captures EOMONTH, Q9. Most months are going to have 5 total "weeks" with either the first or last week being a partial week. For the life of me, I cant figure out how to split each month accurately in Row 9. For example, October 2018 starts with a Monday and Row 8 accurately records the day of the week. How do I make this work? The week always needs to start on MON and end on SUN UNLESS, it is the first or last week of the month....

I hope this makes sense. Working with dates/serial numbers is tricky for me.....

Originally Posted by nwb

Jose,
... The only issue is that for months that start on Sunday would have a first week that started and ended on the same day....

If I understand what you want, it is not possible with 5 weeks
See example

09-01-2019 is a Sunday, then week_1 would end on 09-01-2019 and week_2 would start on 09-02-2019, week_3 on 09-09-2019, week_4 on 09/16/2019 and week_5 would start on 09-23-2019 and end on 28-09-2019, which is not the end of the month.

So, day 09-30-2019 is a Monday and has no week to stay.

As I propose, 01-01-2019 belongs to week_1 (a wide week and is the same as saying that week_1 starts on 02-09-2019). In fact, the days of the week are from Monday to Saturday, as you have defined.