Vacation Accrual Formula Help!

[ewilliams95]

Hello!

I have been assigned to making a vacation accrual spreadsheet that will auto calculate vacation time for the coming year based on hire date. The parameters are complicated and I am stuck on how to do this.

6 months after their hire date they start accruing vacation at a rate of 3.07 every 2 weeks. (the first 6 months they don't accrue)
5 years after their hire date they start accruing at a rate of 4.06 hours every two weeks.

It works out to look like this:
0-1year: 40 hours
1year-4years: 80 hours
+5years: 120 hours

They are able to rollover a max of 40 hours from one year to the next.

Right now I have the first employee in A2, their hire date in B2, Todays date in C2, hours rolled over from previous year in D2

Ultimately, I need a column that will show me on Jan 1 of each year how many hours of vacation each employee will have for the full year based on these parameters!

Any help is much appreciated.

Thanks!

 

[igold]

Hi ewilliams95,

Welcome to the MrExcel Forum.

Won't you also need a column for vacation time used. They may or may not roll over the full 40 depending on time used.

Also saying you get paid every two weeks can be ambiguous. How many pay periods are there in year, 24 or 26. Some companies pay bi-monthly, but people (out of habit) call that every two weeks.