IF formula for allocating overtime within a settlement period

Hello everyone,

I have a rather complicated matter at hand, that I quite frankly have no idea how to deal with.

I have attached a file where I tried to include various possibilities that I need to take into account when coming up with the (I'm guessing) IF formula.

We have 3-month-long settlement periods and we need to count overtime to pay within the settlement period. Unfortunately it's not as easy as reducing time-off in lieu from the overtime within a given month, because within the settlement period the employees can take time-off for say January in February or March, and then they don't get paid for the leftover overtime in January. It's really complicated so I'll try using an example:

No. 1 from the attached file:
January - employee had 12 hours overtime, took 3 hours in lieu, is left with 9 overtime hours to be paid.
February - employee had 5 overtime hours, took 10 hours in lieu, is left with -5 overtime hours. But they used those additional 10 hours to cancel out the previous 9 hours, so what it means is that it's 9-9=0 in January and 5-1=4 in February.
March - employee had 8 overtime hours, took 2 hours in lieu, so they're left with 6 hours to be paid, but what it means is that they get 4-2=2 in February and 8-0=8 in March.

So as you can see below is the original overtime to pay vs the actual overtime to pay:
January - 9 vs 0
February - (-5) vs 2
March - 6 vs 8
It's the same 10 hours but they are allocated differently.

No. 2 in the file is pretty straightforward, but it's there so that the formula can include a formula where there is no time taken off in lieu.
No. 3 in the file has an instance when the employee used up more hours than they could and then used overtime the next month to cover for that.
No. 4 in the file has an instance of there being no overtime one month.

I need a formula that would automatically calculate the "Actual to pay" column for each month in the file that will take into account that there is more or less hours taken in lieu within a given month and that it needs to borrow hours from next month or two in order to settle it. Also it needs to know how much to borrow, like in the example above it uses 9 out of 10 hours in January, and 1 out of 10 in February.

I hope all of this makes sense.

I will really appreciate your help!

Regards,
Nina

i'd recommend to calculate with the accumulation of last 3 months
=SUM(INDEX(B$4:B$800,MATCH($A$3,$A$4:$A$9)-3),INDEX(B$4:B$800,MATCH($A$3,$A$4:$A$9)))

Hi, thanks for your response, I downloaded the file but I'm not sure how it's supposed to work. I input the data into the table you created but the formula above doesn't seem to be doing what I wanted it to be doing. Can you explain? Maybe I'm not doing it right....