Vacation Accrual Formula Help!

ewilliams95

New Member
Joined
Feb 1, 2019
Messages
1
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top