Remember Me?

#1
May 16th 18, 02:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2018 Posts: 4
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post robs3131 Excel Programming 0 September 9th 08 12:29 AM Nazareth Excel Worksheet Functions 0 June 26th 08 09:47 PM Conitha Charts and Charting in Excel 0 June 20th 08 02:53 PM AFroines Excel Discussion (Misc queries) 5 March 22nd 07 02:48 AM [email protected] Excel Worksheet Functions 0 February 15th 06 08:43 PM

All times are GMT +1. The time now is 07:51 PM.