Logic advice for max hours in week that overlaps month

[bradsalmon]

Hi all,

I can't quite get my head around how to achieve something, hence looking for some logic advice and how people think it would work best in VBA.

I have a scheduler which is simply Mon to Fri and number of hours each day that a task is worked on. So some might be 5 hours in a week and others could be 50 hours in a week. Every week I want to double the hours but to a maximum of 15, so the 5 week hour would get 5 additional hours and the 50 week hour would get additional 15. Make sense so far?

My problem is that this has to be calculated monthly and when I calculate the following month it takes no account of the bonus hours that were allocated the previous month in the overlapping week. So where September and October overlap in a week my code at present could potentially allocate 15 bonus hours for the last two days of September and then again for the week when calculating October through the last three days. Make sense?

Here is the code -

Please Login or Register  to view this content.

vbmin is a simple function which returns the smaller of the two numbers being passed to it.

So any ideas how I should reduce the first weeks bonus hours to compensate for any bonus hours claimed for that week in the previous month?

Thanks in advance,

Brad

[protonLeah]

Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[PCI]

bradsalmon,
Welcome on board.
Certainly you notice protonLeah's reminder and to go forward faster please send a sample of your data it will make like easier to check your code.
PCI

[bradsalmon]

thanks protonLeah, I hadn't appreciated that.
Now updated.

[bradsalmon]

Hi PCI,
I did think about a sample of the data, however then I would of had to include more of the code that loops through it as well. So I decided to try and keep it as small as possible by placing the sample data into the code. I understand the downside but hopefully I can work around the checks myself.
Brad