Formula for Calculating (Accrual) Vacation Time/ sick time

[bsayers]

I need to develop a tracking spreadsheet (for 19 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked 2 years or less with the company:
- 3.75 hours biweekly (10 working days a year, earned at 5/6 of day for each full month)
Employees who have worked 3 or 4 years with the company:
- 4.5 hours biweekly (12 working days per year, earned at a rate of 1 day for each full month)
5 years and over with the company:
- 11.25 hours biweekly (15 working days per year, earned at a rate of 1-1/4 days for each full month)

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

[oeldere]

the hours biweekly can be counted with a Vlookup formula.

columns => name / date / descipsion / hours (and in future the formula for year / month / day / weekday)

Then you will be able to get the data easy in a pivot table.

[bsayers]

Thank you! Any ideas how to avoid inflation? Example: employee goes from 2 year status (earning 5/6 of a day per month) to 3 year status (earning 1 day per month)?

[oeldere]

Make another table with the inflation-data.

VLookup can look them up in that table.

There are no salary-promotion involved?