Formula for vacation accrual

Good day everyone:

In my office, the HR representative has to manually calculate an employees vacation days. So I was thinking if it's possible to create a spreadsheet for her to help her out a bit.

I'm seeking some assistance in creating a spreadsheet with some formulas to assist. I'm not that familiar with formulas at all to complete this on my own and therefore seeking some assistance.

These are the conditions of the sick/vacation policy:

1. Each employee is granted 10 paid sicks days every year, but these days aren't carried over into the new year. For sick days, it's on a January - December calender year rather than anniversary year. A new employee has to complete a 3 months probationary period before they're entitled to paid sick days.

2. We consider a 'year' of employment on the anniversary that the employee has been working there. This is for vacation only. For example, in an employee's first day on June 01, 2012, then their anniversary date is June 01, 2013.

3. Vacation days available: A formula that can provide the figure, in days with the following stipulations: (a) after an employee’s 3 months probation, they’re entitled to 10 days paid sick leave (b) during the first 12 months of employment and has worked not less than 110 days, employee may be granted leave with pay at a rate of 1 paid vacation day for every 22 days one which employee worked) if the person isn’t entitled to vacation, based on above criteria, then the 'FALSE' ought to be displayed in spreadsheet.

4. Vacation days to be paid :A formula that can provide the figure, in days with the following stipulations: (a) a worker who has worked more than 220 days in any year of employment, is entitled to 12 days of vacation for that year: or (b) A worker who has worked more than 110 days but not more than 220 days in any year of employment, is entitled to one day vacation leave for every 22 days on which he/she has worked (c) a worker with 6 or more years of service, who has worked more than 220 days in any year of employment, is entitled to 3 normal workings weeks of vacation leave for that year (d) you can’t carry vacation over beyond the third year of employment (e) public holidays cannot be counted in days of vacation leave (d) beyond 15 years of employment, you're entitled to 4 weeks vacation.

So basically, I'd love to be able to enter the employee's start date and then it displays if the employee is eligible for vacation time or not(hasn't completed 110 days/3 months probation). After the probation period, as mentioned above, you accrue 1 day for every 22 days worked. Then based on the persons date of employment date and today's date (date of processing payroll), the spreadsheet/formula should inform me of the number of days accrued to date. Unused vacation days can be carried over, but are deleted if not used after the third year of employment.

Any guidance with formulas would be appreciated.
Thanks
Zan

This should help get you started. I have not factored in the one year constraint, but it should give you some ideas to get you started and work through the vacation piece.

Workbook1.xlsx