LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default Sick time accrual pause for a maximum number of hours reached

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.

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sover -- how do you stop "The maximum time limit was reached..." b robs3131 Excel Programming 0 September 9th 08 12:29 AM
Accrual of hours Nazareth Excel Worksheet Functions 0 June 26th 08 09:47 PM
I want a spreadsheet to track employee vacation/sick time hours Conitha Charts and Charting in Excel 0 June 20th 08 02:53 PM
VACATION/SICK TIME ACCRUAL AFroines Excel Discussion (Misc queries) 5 March 22nd 07 02:48 AM
Vacation/Sick accrual [email protected] Excel Worksheet Functions 0 February 15th 06 08:43 PM

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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017