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.

  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


  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


    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,


    Attached Files:

  2. chirayu

    chirayu Well-Known Member

    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

    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

    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
    which should work on all XL versions
    Last edited: Jan 14, 2019
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    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