I need assistance with the creation of a formula. I am creating an employee vacation accrual worksheet and I need a formula that will do the following:
return a value that equals the actual accrued vacation time for any employee on a monthly basis. I have attached a copy of the worksheet for your review. (see attachment)
In looking at the worksheet, I am currently taking the columns "months employed" and "accrual mode" to determine the actual number of hours accrued (see "hours accrued column). However, as the employee accrues their vacation hours each month they work, they cannot accrue hours greater than 160 hours in total. I attempted to use a MIN Function to do this while still calculating the SUM of the "months employed" and "accrual mode" columns. This Function was returning a value of 160 if the worksheet sum of the "months employed" and "accrual mode" exceeded 160. However, it was still calculating the SUM of the "months employed" and "accrual mode" columns figures in the background.
I need the worksheet to stop calculating when it reaches the the value of 160. In addition, when the employee has used any of their vacation hours, I need the worksheet to deduct their time used from the total value (160). This will undoubtedly cause the total number of accrued hours to fall below 160. I need the worksheet to see this and continue to calculate vacation hours when it detects the "hours accrued" column is less than 160 until it reaches the max figure or cap of 160 hours.
I hope I have provided as much information as you may need. Your assistance would be greatly appreciated.
Bookmarks