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. 🤔

Screen Shot 2022-01-26 at 10.10.43 PM.png

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

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

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

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

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

Screen Shot 2022-01-27 at 12.48.48 PM.png

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

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.

Screen Shot 2022-02-02 at 12.21.10 PM.png

Image 2 - This should be 1 year, 11 months

Screen Shot 2022-02-02 at 12.20.50 PM.png

Image 3 - The formula seems to work for these dates

Screen Shot 2022-02-02 at 12.34.10 PM.png

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

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

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

Screen Shot 2022-02-07 at 2.50.54 PM.png

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?

Screen Shot 2022-02-07 at 3.47.28 PM.png

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.