I am attempting to calculate vacation accrual so that I can enter into
QuickBooks.

Vacation accrues as follows:
1) employee gets 1 week (40 hrs) after 6 month employment
2) employee receives 1 week vacation for every 6 months worked or portion
thereof
3) maximum vacation an employee can accrue is 4 weeks (160 hrs)
4) employee is paid bi-weekly (26 pay periods/2080 hrs annually)

I am attempting to create a formula to calculate the bi-weekly accrual rate
based on the above information.

Thank you

Let's make sure we've got this straight - do they work for 6 months and then
BOOM! suddenly they have 40 hours of leave handed to them? Then they work
another 6 months and WHAM! another 40 hours of leave lands in their lap?

Or are they accruing some leave as they work during these periods? And if
they are, do they accrue leave while ON leave? From your last sentence I am
thinking they are accruing as they are working, although they may not be
allowed to take leave during that first 6 month period? That could be a
management duty: to make sure a person in their first month of employment is
not granted leave even though some time shows on the books.

Basically you are accruing 80 hours of leave per year, 26 pay periods =
80/26 hours per pay period = 3.076923 hours of leave per pay period, MAXIMUM.
So for each hour of work (not on leave) they accrue 3.076923 / 80 = 0.038462
hours (2.307692 minutes) of leave time.

If they work all 2080 hours in a year then they accrue 2080 * .038462 =
80.00096 hours of leave. But if during a year they take 2 weeks off (working
2000 hours) then they would accrue 2000*.038462 = 76.92309 hours of leave.
The missing 3.1 hours is leave they did not accrue while they were on leave
during the year's period.

Does that help any?

"WizTech" wrote:

I am attempting to calculate vacation accrual so that I can enter into
QuickBooks.

Vacation accrues as follows:
1) employee gets 1 week (40 hrs) after 6 month employment
2) employee receives 1 week vacation for every 6 months worked or portion
thereof
3) maximum vacation an employee can accrue is 4 weeks (160 hrs)
4) employee is paid bi-weekly (26 pay periods/2080 hrs annually)

I am attempting to create a formula to calculate the bi-weekly accrual rate
based on the above information.

Thank you

To calculate vacation accrual in Excel based on the information provided, use the following formula:

Here's a breakdown of the formula:

1. The MIN function ensures that the vacation accrual does not exceed the maximum of 160 hours (4 weeks) or the initial 40 hours after 6 months of employment.
2. The MAX function ensures that the vacation accrual does not go below 0.
3. The ROUNDDOWN function calculates the number of 6-month periods the employee has worked, rounded down to the nearest whole number.
4. The TODAY function returns the current date.
5. [Start Date] is the cell reference for the employee's start date.
6. The [*40] at the end of each formula calculates the number of hours accrued per 6-month period.

To use this formula in QuickBooks:

Create a new column in your employee list and enter the formula for each employee. The formula will automatically update based on the employee's start date and the current date. You can then use this information to calculate the employee's vacation pay.