Capping a value with a formula

[INeedHelpThx]

Hello,

I'm trying to improve a tracking tool that was provided to me which helps track our employee sick days that they're entitled to. No employee is allowed to exceed 240 hours of sick leave.

As seen here, the creator of the spreadsheet is utilizing a formula to calculate the available sick hours for this employee, and while he has clearly noted that the max days allowed is 30 (240 hours), the table can still climb over that number if they're already at that cap and they're receiving an extra 8 hours per month, as they're entitled to.

Is there any way to ensure it keeps calculating their sick accruals using the highlighted formula at the top (which is calculating the total for R20, circled in green), but adding in this 240 cap to this formula as well?

Hopefully this is clear enough in my explanation.

Thanks in advance!

 

[Micron]

the table can still climb over that number if they're already at that cap and they're receiving an extra 8 hours per month, as they're entitled to.

Not sure I understand how there can be a cap of 240 while still being entitled to more.
What about wrapping that in an IF function (IF over 240, show this, else show the accumulated total)?
Or use conditional formatting to turn the cell a colour you want when the cap is exceeded?

 

[INeedHelpThx]

Not sure I understand how there can be a cap of 240 while still being entitled to more.
What about wrapping that in an IF function (IF over 240, show this, else show the accumulated total)?
Or use conditional formatting to turn the cell a colour you want when the cap is exceeded?

Sorry, my wording is probably confusing. I mean they can never exceed 240 sick hours in their accrual bank. If the employee already has that cap coming into this year, and this employee shown does, she still gets 8 hours of sick each month, as part of her union agreement. Even though I suppose that's not technically true, since she's already at 240 hours. I believe I found a formula that partially solves my problem:

=MIN(240, SUM(D21:O21)+Q20-(P20))

With this formula, it's never displaying over 240 hours available, but it's continuing to accumulate that 8 hours each month if you enter in the 1 day, so if they received 40 sick hours for Jan - May, it thinks they're at 280 hours, but it's still displaying 240 hours. So if they then use their first sick day in June, it still shows 240 because it then thinks they're at 272 hours.

Not sure I understand how there can be a cap of 240 while still being entitled to more.
What about wrapping that in an IF function (IF over 240, show this, else show the accumulated total)?
Or use conditional formatting to turn the cell a colour you want when the cap is exceeded?

Sorry, I was in the process of sending a lengthy reply, and then I think I found the best solution to my problem. Thank you for your reply, Micron! Much appreciated.