Need help with formula that shows both years and months

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 ✭✭✭✭✭✭

    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 ✭✭✭

    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 ✭✭✭✭✭✭

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

  • SiddV47
    SiddV47 ✭✭✭

    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 ✭✭✭✭✭✭

    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 ✭✭✭


    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 ✭✭✭✭✭✭

    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 ✭✭✭

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


  • SiddV47
    SiddV47 ✭✭✭

    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!