Looking for formula to calculate eligibility date

[chrisdon23]

I have several employees who get a work boot allowance of approx. $250 per year. (Some of the workers prefer to buy much more expensive boots so we allow them to be paid back for the entire amount with the understanding that they won't be eligible for more boots for 2 years instead of 1.) What I'm trying to do is make a formula that will update someone's next eligibility date based on the most recent amount they spent -- in theory, the calculation should be tied to the most recent purchase date too to prevent someone from showing as eligible earlier in the year than they should be.

We aren't terribly strict on the dollar limit, for example if someone were to hand in a receipt for $260 or $280, we'd still pay them the entire amount -- they would become eligible again at least 12 months from the date of that purchase. If someone hands in a receipt for $500ish,they wouldn't become eligible again for 2 years from the date they purchased them.

[BadlySpelledBuoy]

Hi and welcome

Without a sample workbook provided it's not easy to envisage the scenario properly, but have a look at the attached for my best guess given the info provided.
Formulas in the yellow cells essentially look at each name in the list and find the value of the latest boots they claimed for. If that value is less than $300 it will add 12 months to the last claim date. If more than $300 it will add 24 months to the last claim date.

Obviously I've picked $300 as an arbitrary amount but you can easily change that in the formula to something more suitable.

Not sure if it helps or not, but if not, a sample workbook would be great.

BSB

[chrisdon23]

I like your layout of having everyone's due date in a column together; the way I had mine set up was each person having their own column. I've attached the one I started. (Thanks so much for your help -- I would never have been able to figure this one out on my own).

[BadlySpelledBuoy]

Happy to help. That's what we're here for

I'd have provided a similar, but slightly different answer had I seen your layout first. But the idea is the same, find the last date for a given employee and make a calculation based on the price of the last claim. Just in a different format to mine, so you wouldn't need the MAXIFS, you could just use MAX as it's one column per employee.

But if working with data for almost 30 years has taught me anything, it's keep your data in one big list rather than split out into lots of separate lists. Far easier to make calculations on it that way

BSB

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.