1. Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return value between dates

Discussion in 'Ask an Excel Question' started by Kelli Webb, Jan 14, 2019.

1. Kelli WebbMember

Messages:
33
Hi,

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

Kelli

File size:
14.7 KB
Views:
7
2. chirayuWell-Known Member

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

File size:
17.6 KB
Views:
11
3. Pete MccannMember

Messages:
101
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.

File size:
15.1 KB
Views:
5
4. pecoflyerActive Member

Messages:
291
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
Last edited: Jan 14, 2019
5. Peter BartholomewWell-Known Member

Messages:
584
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.

File size:
19.3 KB
Views:
5