Return value between dates

[Kelli Webb]

Hi,

I've prepared two examples if you believe there are different methods that could be applied, ideally keeping it simple yet effective.

Thanking you greatly in advance,

Kelli

 

[chirayu]

I've only edited sheet 1 but u can use the same principle for the other sheet

 

[Pete Mccann]

Hi Kelli. Here is an alternative (more cumbersome calculation) that does the same thing as Chirayu. I've also used the first sheet. Is your estimate of Vicki's Desired result correct? You say this should be 20 years but the Age Result is actually 19.81 - I think this puts it into the "10 years" logic bracket.

 

[pecoflyer]

Your example is not consistent.
Your table A:D states that 60 or more should return "60+years"
In your Desired output I find "60+years", "60 years" ( although the outcome is 62) and "70 years" ???

To stick exactly to your table try
=VLOOKUP(MIN(FLOOR(DATEDIF($G2,TODAY(),"y"),10),60),$C$2:$D$7,2,0)
which should work on all XL versions

 

[Peter Bartholomew]

You were looking for alternatives?
Like @pecoflyer, I concluded that, as your ranges get more irregular, lookup from a table becomes better than a formula.
Something that can be done with the bands of service years is to use Number formatting to add the "years" and the "<" & "+" operators.