To calculate vacation accrual in Excel based on the information provided, use the following formula:
Formula:
=MIN(40,MAX(0,ROUNDDOWN((TODAY()-[Start Date])/180,0))*40)+MIN(120,MAX(0,ROUNDDOWN((TODAY()-[Start Date])/180,0)-1)*40)
Here's a breakdown of the formula:
- 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.
- The MAX function ensures that the vacation accrual does not go below 0.
- The ROUNDDOWN function calculates the number of 6-month periods the employee has worked, rounded down to the nearest whole number.
- The TODAY function returns the current date.
- [Start Date] is the cell reference for the employee's start date.
- 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.