Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
...
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sick time accrual pause for a maximum number of hours reached | Excel Worksheet Functions | |||
Sick Time Reports | Excel Worksheet Functions | |||
VACATION/SICK TIME ACCRUAL | Excel Discussion (Misc queries) | |||
Pausing and resuming a running macro | Excel Programming | |||
Vacation/Sick accrual | Excel Worksheet Functions |