Hello, new to the forum, thanks for allowing to join. :-)
Presented with a problem from HR concerning MAX Accrual Rate for vacation allotment.
Here is the scenario...Employees are categorized for how many years of service they are allotted for MAX Accrual Rate for Vacation. So for an example an employee who has 11 years of service, their rate is 10.67 hours per month vacation time. The MAX Accrued Hours that employee can have on the record is 384.12 (10.67 x 12 x 3) total and not to exceed that amount for each month. So when our spreadsheets are created for each employee, they have a beginning total from the previous year (example: 150 hours).
What HR would like to see the spreadsheet formula accomplish is this: ADD the TOTAL amount of vacation time taken (in one month period - each employee submits a spreadsheet for time taken during said month, EXAMPLE, employee took a week's vacation 40 hours), then SUBTRACT the Vacation HOURS USED from previous month's total, THEN ADD the accrual rate (10.67) THEN if that subtotal exceeds MAX Accrued Hours allotted, then Grand Total ONLY shows the MAX Accrued Hours, otherwise, the formula would ADD the sub total and then display. Then replicated that formula to each month on a spread sheet that shows the entire fiscal year.
Bookmarks