I have a tab in a spreadsheet that lists employees taking their vacation in advance. If an employee accrues a week of vacation late in the year and there aren't any more weeks for them to pick due to the limit of vacations being reached for that week, the company will allow them to take it at some point prior in the year with zero pay and get paid once they actually accrue the hours at the anniversary. In one of the cells, I have a lookup formula combined with an Edate formula that adds 12 months to their start date so we know what date they will accrue the vacation this year. Here is the problem I am running into now: I have people who are getting their second weeks of vacation which is earned at 3 years. The formula I am currently using reads:
=IF(A4="","",EDATE(INDEX(Warehouse!$H$2:$H$277, MATCH(A4,Warehouse!$B$2:$B$277,0)),12))
Rather than me changing the Edate formula on a case by case basis from 12 to 36, is there a better formula I can use that could encompass both scenarios? I do have another few columns in that tab that the lookup references that lists when the employees will accrue their 1st, 2nd, and 3rd weeks if it would be better to somehow use those instead?
=IF(A4="","",EDATE(INDEX(Warehouse!$H$2:$H$277, MATCH(A4,Warehouse!$B$2:$B$277,0)),12))
Rather than me changing the Edate formula on a case by case basis from 12 to 36, is there a better formula I can use that could encompass both scenarios? I do have another few columns in that tab that the lookup references that lists when the employees will accrue their 1st, 2nd, and 3rd weeks if it would be better to somehow use those instead?
