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!
Answers
-
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"
-
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.
-
You need to remove the = from before the second ROUND function.
-
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
-
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"
-
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!
-
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"
-
Thanks, Paul. Unfortunately, this formula is not consistent across all the dates. Please see the screenshot below:
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!