View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_3_] MyVeryOwnSelf[_3_] is offline
external usenet poster
 
Posts: 56
Default Help with Sick time accrual formula for pausing when a max isreached and resuming when time is used

...
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.


One possibility is the calculate "sick remaining" first and then calculate "accrued" as "'sick remaining' minus 'previous sick remaining'"

Calculating the new "sick remaining" first could incorporate your above IF(...) expression, and use MIN(), something like
=MIN(((previous row's sick remaining)
- 'hours used'
+ IF(Table25910[...]$V$4,$U$14,0)),
480)

Then the "accrued" calculation is just the difference between two cells:
"sick remaining" minus "previous sick remaining"