I am looking for a sample spreadsheet that is a month to month accrual for vacation.
Our employees earn the following:
1-3 years is 5 days
4-10 years is 10 days
10+ years is 15 days.
accrue based on Date of Hire
I would also need to enter days taken and show the balance at the end of the month.
They are eligible to roll over only 15 days and once they hit that 15 days they stop accruing.
They also do not become eligible until their one year anniversary.
Thank you in advance
Welcome to the forum.
Is the accrual monthly or is it granted in a lump amount? For example does an employee who has just completed 1 year of service start accruing leave at the rate of 0.416666667 days per month or is 5 days granted for the coming year in advance?
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
It is accrued by month and not a lump sum
Here is something to play around with. I don't know how you are going to enter your legacy data to start with so I just took a guess that you would enter the employee name and a date after the start date with the total days taken. Assuming that the rules of carry-over have been followed every year, this might work. I say might because I haven't been able to do any testing. In any event it may give you some ideas.
All leave taken is entered on the Leave Taken worksheet. The Employee worksheet should make the calculations for the current year (rolling over I hope). This was done in a bit of a hurry so no testing.
Last edited by newdoverman; 07-05-2016 at 09:00 PM.
OMG thank you so much and I will see if this works, but either way I will let you know
Thank you once again
I created a video on calculating vacation entitlement; check it out it may help https://youtu.be/MF_ZFt6p2ZQ
http://www.easyexcelanswers.com
I have made a correction to the Employees worksheet that eliminates the filling in of erroneous data when a new employee is entered. A lot more data is needed to see if the carry-over works.
Hello Newdoverman,
This will work my only question is that our accrual is based on a calendar year and not anniversary date. The only thing that is determines by the anniversary date is for new employees and when they are eligible to use their accrued vacation.
All employees accrue from date of hire.
Please advise
Give this a go. I changed column D to give the number of months completed in the calendar year. December isn't counted until January arrives and the is counted as 12. January will be 1 on Feb 1 etc.
Sorry, my time is extremely occupied so unfortunately I can't do much if any testing for you. I am just hoping that you find any errors in logic or formulae and are able to correct them.
Thank you for all your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks