I have changed the formula to hopefully meet the criteria. Here is the updated formula:
=IF(D2<4,0,IF(D2<2*26,MIN(80,D2*1.54),IF(D2<7*26,MIN(80,2*26*1.54+(D2-2*26)*3.08),IF(D2<14*26,MIN(120,2*26*1.54+7*26*3.08+(D2-14*26)*4.62),MIN(160,2*26*1.54+7*26*3.08+14*26*4.62+(D2-14*26)*6.15)))))
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.
Bookmarks