HoursRemainingPerMonth.xlsx

Okay so some background... I am trying to come up with a forecasting tool that predicts how many hours someone will work in each month going forward. I decided I would just use a simple check for hours remaining in a month. I have a complex formula that basically just checks to see if there are enough hours remaining in a month to cover the hours someone has left and then decides which month to put the hours in based on how many hours are left in the month.

It works GREAT for someone who works 40 hours a week.

Here is my problem...How can I change the formula to calculate the number of hours remaining in a month if they work less that 5 days a week. I want it to be based on a start date...

I.e. Assume someone starts on the first of the month, and that the date falls on a Monday and there are 160 workable hours in the month (4 weeks). I want to show that for someone that works 4 days a week, there are 128 hours to work on Monday the 1st, then 120 for Tuesday, 112 for wed, 104 thrus. Then, I want it to skip to the next Monday and pick up with 96, etc etc etc...However, the start date could be a tuesday, wednesday, or any day of the week, so I can't just do a blanket skip every xyz days.


Any thoughts?