If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vacation Accrual
I am re-posting this from Friday & Monday. I got a
response, but it didn't solve my problem. I would appreciate any advice. Thanks. An employee is eligible for 120 hours of vacation per year, accrued from their date of hire or anniversary date. They can carry over unused vacation time from one year to the next. However, once they've reached two times their eligible vacation hours,they stop accruing until they drop below, in this case, 240 hours. Once their total drops below 240 then it will start to accrue again. Once they stop accruing, they do not earn any vacations hours, they cannot go back and recoup any hours lost due to reaching their hours cap. The accrual rate is 2.3076 hours per week. Here's my formula, is their any way that I can get it to stop and start accruing again based on how many available hours they have? A1 = date of hire A2 = Anniversary Date A3 = hours carried over from prior year A4 = Eligible hours A5 = Accrual Date A6 = =if(A4=120,($A$5-A2)/7*2.3076,0)(accrued hours from anniversary date) A7 = =if(and(A4=120,A6+A3-A8240),240,A6+A3) A8 = Used vacation hours My formula in A7 works except that when the hours drop below 240, it adds the amount in A6. However, the amount in A6 is the actual hours that would have accrued from the anniversay date to the accrual date, which is incorrect, because it's including hours that should not have accrued because of reaching their cap. The accrued hours should stop as long as the total is at 240, then start again when it drops below. The lost accrual hours should not be recovered once the accrued hours drops below the 240. I hope this make sense. Any help would be very much appreciated. Thanks. |
#2
|
|||
|
|||
Vacation Accrual
I don't believe what you want can be done without addint additional lines to
your spreadsheet. In your current spreadsheet, there is no way for Excel to know what happened in prior years related to whether vacation time stopped accruing. It sounds as if you need more of a database solution for this that would be able to keep historical information. If you wanted to use Excel, you would have to add a line showing total hours accrued up to the prior year end then add what should be accrued for the current year. I suppose you could also maintain separate sheets for each employee and add a new column for each year (from the employee hire date) that would calculate what you want. Email me directly if you need any additional assistance on this matter. Mike. -- Michael J. Malinsky Pittsburgh, PA "I was gratified to be able to answer promptly, and I did. I said I didn't know." -- Mark Twain "Phyllis" wrote in message ... I am re-posting this from Friday & Monday. I got a response, but it didn't solve my problem. I would appreciate any advice. Thanks. An employee is eligible for 120 hours of vacation per year, accrued from their date of hire or anniversary date. They can carry over unused vacation time from one year to the next. However, once they've reached two times their eligible vacation hours,they stop accruing until they drop below, in this case, 240 hours. Once their total drops below 240 then it will start to accrue again. Once they stop accruing, they do not earn any vacations hours, they cannot go back and recoup any hours lost due to reaching their hours cap. The accrual rate is 2.3076 hours per week. Here's my formula, is their any way that I can get it to stop and start accruing again based on how many available hours they have? A1 = date of hire A2 = Anniversary Date A3 = hours carried over from prior year A4 = Eligible hours A5 = Accrual Date A6 = =if(A4=120,($A$5-A2)/7*2.3076,0)(accrued hours from anniversary date) A7 = =if(and(A4=120,A6+A3-A8240),240,A6+A3) A8 = Used vacation hours My formula in A7 works except that when the hours drop below 240, it adds the amount in A6. However, the amount in A6 is the actual hours that would have accrued from the anniversay date to the accrual date, which is incorrect, because it's including hours that should not have accrued because of reaching their cap. The accrued hours should stop as long as the total is at 240, then start again when it drops below. The lost accrual hours should not be recovered once the accrued hours drops below the 240. I hope this make sense. Any help would be very much appreciated. Thanks. |
Thread Tools | |
Display Modes | |
|
|