Auto Calculate Workdays for Table

[alwaysneedofhelp]

I've more or less got this spreadsheet where I need it to be but was hoping to automate one last function. I've got it down to where my employees only need to enter in data into the yellow boxes. The first is the beginning of the current month and the second is the Monthly Admission Goal. From there it will auto calculate the amount of workdays (M-F) in the month and use that number to calculate how many daily referrals are needed to reach the goal. As they enter in data into the table it continues to adjust. All of that is working well (save a #DIV/0! error in cell H5 if they happen to meet their goal, in which case a zero now becomes part of the equation and it throws it off. I thought the MAX command for H4 would suffice and it's working appropriately for that cell but it still throws an error for H5. I'm assuming an IFERROR command could fix this but I have yet to figure that out.)

What I'd like to do is find a more precise solution for calculating the weekly goal column (H7 to H11). As you can see it simply divides the H1 cell into fifths. Ignore the /.75 portion of the formula as this is used to account for 25% loss from referral to actual admit. The issue with this is it does not account for shorter work weeks at the beginning and end of the month. For instance, June 2022 began on a Wednesday and will end on a Thursday. I'd like to have the weekly goal column calculate this appropriately. The most elegant solution is to likely build a new table that is actually based on a dynamic calendar but I've never done this before and had already built this one out pretty thoroughly before noticing the issue. A second, less elegant solution might be to come up with one or two more cells that require user input (for instance 'What day of the month does this month begin and end on') and go from there. As you can see I've copied formulas for each calculated cell over to the side (at bottom for the lower cells) if that help....

Looking for opinions, suggestions or solutions! I embedded a pic below and attached the workbook.

Referrals.jpg

[alwaysneedofhelp]

Is this not reasonably possible? Or perhaps a little too complex for the type of problems typically asked of the forum?

[dosydos]

what about in cell H7 and then drag down..

=SUM($H$1/0.75/COUNT($B7:$F7))

[alwaysneedofhelp]

what about in cell H7 and then drag down..

=SUM($H$1/0.75/COUNT($B7:$F7))

I took your suggestion and altered the formula a bit to better suit the point of the tracker. In this case "=SUM(H3*COUNT(B7:F7))" worked well for my needs. I've given up on the idea of having the grid auto calculate the M-F working days when the month is adjusted. But that's no big deal because it's easy enough for me to build out the workbook with a specific monthly sheet that I've already pre-filled and protected and send out each year. I've also removed the cell attempting to auto-adjust to new daily referral goal as it was a complicated formula and frankly a little defeating for the end user if you have a bad few weeks.

So now I have a working system for giving the employee an idea of their goals on a week to week and month to month basis, so they can remain focused/motivated even if they are behind for the month. I've now protected all cells except the Monthly Admission Goal and grid spaces that correspond to specific monthly calendar work days. One last issue though, if a zero is deleted or removed from the grid...I need it to default back to zero. The zero entry is vital to the COUNT function and if an employee removes one, leaving it blank, and forgets to replace with their desired number or back with a zero it throws the whole thing off. This has to be a fairly common requirement and should be an easy enough but can't seem to find the right Google search terms to correct this. I've reattached, if helpful...

[alwaysneedofhelp]

what about in cell H7 and then drag down..

=SUM($H$1/0.75/COUNT($B7:$F7))

BTW thanks your help...it got me going in the right direction...