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"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!