Vacation Accrual Formula

Hi Everyone,
I’ve been revamping our vacation tracking log to accommodate new rules and having a major issue. Attached I’ve uploaded what I’ve got so far.

The rules are as follows:

Less than 7 years Seniority you get 15 Days’ Vacation (120 Max Hours)

8 to 13 years Seniority you get 22 Days’ Vacation (176 Max Hours)

14+ years Seniority you get 25 Days’ Vacation (200 Max Hours)

Every pay period you accrue time, if you reach your max you stop accruing until you go under the max.

The formula in the tab 2012 schedule column JG is the same formula broken out into 7 smaller formulas in Accrual Dates tab in column M except for a wrapped if statement saying if you reached your max then show this (this is what I need some guidance on) and the today() function is removed for testing purposes.

My issue is the calc I’ve created doesn’t take into effect the max properly. Basically if the employee has reached his max then this time is lost. (I.E. my example in Accrual Dates column F). Has anyone had this issue before? I’m not sure how to solve. Any help is greatly appreciated.

Thanks,
Cullen8

Your formula doesn't take into effect the max properly because the max isn't defined anywhere. You can simply insert an if/then formula in column F, where if the calculated amount >= max, show max. But in order to do that, you need to first define the max value for each employee.

Hi Mcmuney,

Thanks for the reply, however, max isn't the solution (I’ve used max (technically used an if statement) in column JG in the other tab). Max doesn't stop the accrual calc which continues to grow every pay period. Hope this makes since. If needing another example let me know.

Thanks,
Cullen