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 Webb

    Kelli Webb Member

    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.

    Thanking you greatly in advance,

    Kelli

    Attached Files:

  2. chirayu

    chirayu Well-Known Member

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

    Attached Files:

  3. Pete Mccann

    Pete Mccann Member

    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.

    Attached Files:

  4. pecoflyer

    pecoflyer Active Member

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

    Peter Bartholomew Well-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.

    Attached Files:

Share This Page