Bi-Weekly Vacation Accrual

[mindylavery]

I am trying to set up a spreadsheet to track our company's vacation accruals. The way our policy works is we accrue hours every pay check and we get paid every other Thursday. After one year of employment you will have earned 40 hours vacation, after 2 years of employment you will have earned 80 hours vacation, etc. The breakdown of the hours accrued are as follows:

0-2 years: 1.54 hours per pay check
2-7 years: 3.08 hours per pay check
7-14 years: 4.62 hours per pay check
14+ years: 6.15 hours per pay check

Our vacation policy is also based off of anniversary date. So for example, John started 08/01/13 so he already has 80 hours available to him, plus he has already accrued 33 hours from 08/01/15-01/05/16. I can't seem to get any formula to reflect this.

Thanks,

[JeteMc]

Here is a link to a thread about tracking vacation accrual, spreadsheet attached in post #4. If you need something more custom made to your circumstances please "Go Advanced" (button is below the "Quick Reply" box) and use the paperclip icon of the resulting page to upload a desensitized sample of your spreadsheet.

http://www.excelforum.com/excel-gene...readsheet.html

[mindylavery]

Vacation.xlsx

I have attached my spreadsheet. The hours under the vacation available is exactly how many hours that I have accrued since my anniversary date. The only way that I can find to update the hours each pay day is to manually add the number of weeks from anniversary date to pay date. I would like this to automatically update because every employee has a different anniversary date and it would be so time consuming to manually add up how many weeks for each employee.

Thanks,

[JeteMc]

I am confused. In F3, under the heading Vacation Available, there is a formula. The formula uses the Datedif function (which I do not have on my version) which takes today's date (Today()) as an argument, so it should be updated automatically, everyday, whether it is a payday or not.
Are you saying that the formula gives the wrong result? If so can you tell me what number you expect to see in F3 as of today?

[mindylavery]

In the formula in F3, I had to add *37 to the end of the formula in order to get the vacation hours accrued from Aug until Jan. When I took it *26, it only came up with 80 hours, which would be correct if we were going by years of service but we go from Anniversary date so I had to add 11 to the 26 because we have had 11 pay days from Aug 1 until now.

[JeteMc]

Ok, take a look at the attached file. I added a Pay Periods of Service which, as there are 14 days in two weeks, uses the formula

Please Login or Register  to view this content.

It then calculates the accumulated days of vacation using the formula

Please Login or Register  to view this content.

It gives a different (more favorable) answer from what you originally had.
Copy of Vacation.xlsx
Let me know if you have any questions.

[mindylavery]

Thank you so much! That works perfectly!

[JeteMc]

You're welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a good day.

[mindylavery]

Vacation.xlsxOk I have one more question. We are able to "bank" up to 80 hours of vacation at any given time. On an employees anniversary, they go back down to the amount that they have earned. For example, I have been here two years so I have earned 80 hours of vacation. If I haven't used any vacation by 08/01/16, I will bank those 80 hours and will have another 80 hours available to me. Come 08/01/17, if I haven't used those 80 hours, I will still only have 80 hours until I reach my 7th year, then I will go up to 120 hours each year. So the most that I should ever have available to me (until my 7th year) should be 80 hours. How can we reflect that in our current formula? Another example, we have an employee that has been here over 14 years, so she should have 160 hours available. Our current formula shows her as having 2444.441 hours available. Another issue I forgot about is that an employee has to be here 2 months in order to receive vacation hours. Right now, we are showing everyone has vacation time available. I have re-attached the spreadsheet.

Thanks

[JeteMc]

I have changed the formula to hopefully meet the criteria. Here is the updated formula:

Please Login or Register  to view this content.

Here is a copy of your file with the formula applied: Copy of Vacation-1.xlsx
I hope that you will not take offense, but I am glad that I didn't work for your company. I was able to bank all of my unused leave toward retirement, the incentive meant that I didn't miss many days over 35 years and according to my employer saved them a lot of money.
Let me know if you have any questions about the formula.