Rolling formula for each week to check for sickness - LET/COUNTIFS

Hi,

I'm trying to have a formula to calculate my sick paid and sick unpaid. The allowance for the whole year is 3 days paid=24 hours. The rest needs to go to unpaid column. However, in each period this rule needs to be checked for in the previous period as well. If I reach 24 hrs in week 1 and I am sick again in week 2, the sick leave will go to unpaid.

I've been using the below but it keeps transferring to each week - so I have 3x SL in PERIOD1WK1 and I can see the same amount of sick hours again in wk2. In week 2 this column should equal to 0 and Sick unpaid should kick in.

=LET(prevSickPd,SUMIFS($K8:W8,$K$7:W$7,"Sick Paid"),totalSickPd,COUNTIFS($K8:W8,"SL",$K$7:W$7,"Shift")*8,currentSickPd, totalSickPd - prevSickPd,MEDIAN(24-prevSickPd,currentSickPd,0))

Thank you.


Lucie.

Hi Glenn, thanks for checking. Yep, I want it to be checking the weeks in this manner - as in take into consideration the cumulative amount. But if they are sick in one week - let's say 8 hrs, I only want to see the hours in sick paid for that specific week. If the 8 hrs go automatically to next week, it affects the next week's payroll because it shows they need to be paid for 8 hrs sick leave. I'm not sure if the explanation I give makes sense.