Sick time accrual pause for a maximum number of hours reached
 

Hello,
I work in HR and assistance with my excel spreadsheet for accruals. I have it set up that on each sheet there are three tables, Vacation, Sick, and Personal. (This allows me to see all of one employees available time easily.) My problem is that we have a max amount on our accrued Sick time. We can only have 480 hours. I need to know how to



Sick Available Hours Used Date of Used Accrued Sick Remaining
106.39 3.69 110.08
110.08 9.75 1/11- 1/12/17 3.69 104.02
104.02 3.69 107.71
107.71 3.69 111.4
111.4 12.5 2/22/, 3/1/17 3.69 102.59
102.59 2.75 3/6/2017 3.69 103.53
103.53 12.75 3/23, 3/31/17 3.69 94.47
94.47 3.69 98.16
98.16 8 4/17/2017 3.69 93.85
93.85 3.69 97.54
97.54 8 5/22/2017 3.69 93.23
93.23 3.69 96.92
96.92 3.69 100.61


Currently my "Accrued" column in my table has this formula:

=IF(Table25910[[#This Row],[Accrual Date]]$V$4,$U$14,0)

$V$4 = Anniversary date
$U$14 = 3.69 hrs of accrual


I would like to know how to write the formula so that it will accrue at the regular rate of 3.69 each payperiod until someone reaches the max 480 hours. At that point I would need it to only add the amount needed to get to the 480 as well as not add any accrued time while the employee stays at the 480. Then to begin adding the accrued amount back once they use below the 480 hours.

I do know that I can do a MIN in the Sick Remaining column, but it still shows an accrual amount. I'd rather set the formula up in the accrued column.
Any help would be fantastic.