Vacation Accrual formula

[vane0326]

Hi everyone,

I need some help on vacation accrual. I'll try to explain as best as
I could.

Employees accrue vacation hours on a weekly basis as follows:

If the employee works more than or equal to 24 hrs. of that week then
it will accrue to the following,

( Hiring Date )

0 – 1 years .77 per week for a total of 40 hours of vacation
time.

2 – 7 years 1.54 hrs per week for a total of 80 hours of
vacation time.

8 – 15 years 2.31 hrs per week for a total of 120 hours of
vacation time.

16 – 25 years 3.08 hrs per week for a total of 160 hours of
vacation time.

26 - + years 3.85 hrs per week for a total of 200 hours of vacation
time.

Employees accrue the full weekly benefit if they have at least 24
regular hours processed through payroll. Regular hours include holiday
hrs and vacation hours i.e., employee is on vacation and Its process the
vacation hours through payroll they will accrue the maximum vacation
hours allowed.


In cell D25 is the formula I would like.


+-------------------------------------------------------------------+
|Filename: Vacation Accrual.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4785 |
+-------------------------------------------------------------------+

 

[joeu2004]

I need some help on vacation accrual.

I did not look your spreadsheet. If A25 contains the number of years
of service, the vacation accrual rate could be, in B25:

=lookup(A25, {0, 2, 8, 16, 26; 0.77, 1.54, 2.31, 3.08, 3.85})

Pay close attention to comma, semicolons and curly braces in that
formula.

For any particular week, if C25 contains the number of hours worked
in that week, the amount of vacation time accrued for the week could
be, in D25:

=if(C25 < 24, 0, B25)

How you determine number hours worked in a week is a completely
separate matter.

HTH.


----------