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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,346
Latest member
mmoose

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
Top