Hi, I'm looking for a way to more automate a distribution of a balance of PTO hours across a designated number of weeks. The total hours someone is entitled to per week populates via formula in F29. H29 has a listing of how many total weeks they are entitled to, K29 adds a flat 8 hours onto the overall total(this actually gets added to their first week only. Finally M29 gives a grand total of all weeks. This is pictured below:
What I want to do is in column P starting in row 19 is list the weeks and have balances automatically populate to them. In a simple scenario, that would look like this:
When you total up all of these weeks, they should equal 168.0 (M29). Notice that the first week is higher than the others - it would have 8 hours from K29 added to it. Only the first week gets this, so that week will always be 8 hours higher than teh rest. The rest would be distributed among the remaining 5 weeks so that their combined total equals M29.
Here is what complicates the process further. The person can opt to designate 1 to 2 weeks as what is called "Split" weeks (S). If someone elects to do that, then a flat 40 hours is reserved for that week and then the calculation is redistributed across the remaining weeks. If that were the case in the above scenario, it would look like this:
So in this case because column Q has "S" next to a week, that should automatically allocate 40 hours to that week's value and the rest of the weeks should recalculate, dispersing the remaining value across the rest of the weeks and of course adding the 8 hours to the first week, all so that it totals M29. There could be instances where a person is not entitled to 6 weeks, it could be less, so that is another consideration.
Is something like this even doable with VBA?
What I want to do is in column P starting in row 19 is list the weeks and have balances automatically populate to them. In a simple scenario, that would look like this:
When you total up all of these weeks, they should equal 168.0 (M29). Notice that the first week is higher than the others - it would have 8 hours from K29 added to it. Only the first week gets this, so that week will always be 8 hours higher than teh rest. The rest would be distributed among the remaining 5 weeks so that their combined total equals M29.
Here is what complicates the process further. The person can opt to designate 1 to 2 weeks as what is called "Split" weeks (S). If someone elects to do that, then a flat 40 hours is reserved for that week and then the calculation is redistributed across the remaining weeks. If that were the case in the above scenario, it would look like this:
So in this case because column Q has "S" next to a week, that should automatically allocate 40 hours to that week's value and the rest of the weeks should recalculate, dispersing the remaining value across the rest of the weeks and of course adding the 8 hours to the first week, all so that it totals M29. There could be instances where a person is not entitled to 6 weeks, it could be less, so that is another consideration.
Is something like this even doable with VBA?
