Need help with formula that shows both years and months

Options

Hi,

I need help with setting up column formulae for the two (yellow highlighted) columns. I have included the sum fields for each of the columns. Please suggest a formula so that one may see both the years and months of the grant in the column/ cell.

For example for the given start and end dates, the Original Length of Grant column must show 2 years, 6 months (2 yrs, 6mo), and Length of Grant including extension column must show (2 yrs, 10mo).

The current formula I have only shows the total months. 🤔

Thank you!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you already have a total months formula, you could use something like this...


    =total_months_formula / 12 + "yrs " + MOD(total_months_formula, 12) + "mo"

  • SiddV47
    SiddV47 ✭✭
    Options

    Hi Paul,

    So in summary -

    =ROUND((NETDAYS([Grant Start Date]@row, [Grant End Date]@row) / 365) * 12) / 12 + "yrs " + MOD(=ROUND((NETDAYS([Grant Start Date]@row, [Grant End Date]@row) / 365) * 12), 12) + "mo"

    This shows #invalid operation.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You need to remove the = from before the second ROUND function.

  • SiddV47
    SiddV47 ✭✭
    Options

    The formula worked. But as you can see below it now shows as 2.5 years and 6 months. I would like to avoid the decimal in the year. What I am expecting is like 2 yrs, 6 months as I mentioned in my original question. Please let me know if this can be done.

    Best regards,

    Sidd


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Apologies. Forgot another ROUND function around the year calc.:

    =ROUND(ROUND((NETDAYS([Grant Start Date]@row, [Grant End Date]@row) / 365) * 12) / 12) + "yrs " + MOD(ROUND((NETDAYS([Grant Start Date]@row, [Grant End Date]@row) / 365) * 12), 12) + "mo"

  • SiddV47
    SiddV47 ✭✭
    Options


    Hi Paul,

    Thank you for your help. I ended up using your formula. But there seems some discrepancies in the calculation. Please see the images below:

    (FYI Grant start date is now Start Date and Grant End date is now Original End date)

    Image 1 - This must be 8 months. But the formula shows 1 yrs, 8 months.











    Image 2 - This should be 1 year, 11 months













    Image 3 - The formula seems to work for these dates















    Another thing I request is if there are not dates inputted in either the Start Date or the Original End date columns, I DO NOT want the Original Duration of Grant Formula to shows this error - #INVALID OPERATION. The column should not show any value.

    Can you please help me resolve these issues.

    Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Ok. I was trying to just adjust what you were already working with, but lets try a different formula instead.


    =IF(YEAR([Start Date]@row) = YEAR([Original End Date]@row), 0, YEAR([Original End Date]@row) - YEAR([Start Date]@row) - IF(MONTH([Start Date]@row) > MONTH([Original End Date]@row), 1, 0)) + "yrs " + IF(MONTH([Start Date]@row) > MONTH([End Date]@row), 12 - MONTH([Original End Date]@row) - MONTH([Start Date]@row), MONTH([Original End Date]@row) - MONTH([Start Date]@row)) + "mo"

  • SiddV47
    SiddV47 ✭✭
    Options

    Thanks, Paul. Unfortunately, this formula is not consistent across all the dates. Please see the screenshot below:


  • SiddV47
    SiddV47 ✭✭
    Options

    Paul,

    Can we try to resolve this by, for example calculating the NET DAYS and then converting the days to years and months? Please let me know if there is a formula for this?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!