Vacation accrual Spreadsheet

T

TimBeau

I am working on a spread sheet for my Company. I have looked at many examples
but I am still not finding what I need with the specific perimeters involved.
the facts:
PDO accrual rate increases every employee anniversary.
PDO accrues every weekly pay period worked.
PDO accrues after 90 days of employment.
PDO max accrual is 120 hours.

PDO incremental rate accrual as follows:
1st year----2.0 hrs per pay period
after first year-----2.15
after second yr----2.3
after third year----2.46
after fourth year--2.61
after fifth year-----2.76
after sixth year----2.92
after seventh yr---3.07
after eigth yr------3.23
 
B

Bob Phillips

=IF(TODAY()-D5<90,0,MIN(120,DATEDIF(D5,TODAY(),"M")*CHOOSE(SUMPRODUCT(--(DATEDIF(D5,TODAY(),"M")>{0;1;2;3}*12)),2,2.15,2.3,2.46,2.61,2.76,2.92,3.07,3.23)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

TimBeau

Bob,
Thanks for the formula. I copied it into a spreadsheet but im not sure how
the cells should be set up to read to get the result.
if I could impose upon you good nature....thanks
 
B

Bob Phillips

Tim,

In my tests, I just set the employee's start date in D5, and then plugged in
that formula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

It needs a login, and I don't have one.

Perhaps the 120 is because they have been there long enough to accrue the
max, 120.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

TimBeau

Thanks, but I may not have been clear. I am having a difficult time getting
this issue solved
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top