Convert count of days to months
Hi,
I have a "Contract end date" field. I used the formuale to calculate the "remaining days" by using Today - Contract Date that gives a days value . Eg. 100
Questions - Need to convert this value of 100 to Months and days format eg. 3 months and 10 days
Can you please confirm
Thanks!
Best Answer
-
Hi
If [Days to Contract Renewal]@row = 653
Then
=INT([Days to Contract Renewal]@row / 30) + " Months " + ROUND((MOD([Days to Contract Renewal]@row / 30, 1) * 10), 0) + " Days"
You can split this up into two formulas in two columns if you want to have each number broken out.
I hope this helps.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
Answers
-
Hi,
There are probably several ways to do this that come to mind right away. You could if it's acceptable to estimate, use a number that represents a standard month ( 30.44) but that will not be exact.
If you need to be exact (assuming you will adjust for leap years), you can set up a Lookup table for each month, I think this is easier and allows me to set a reminder to change it for leap year OR I can have a number of years in the table. Then I get the number of days in the month, for both, the number of days in left in this month, the number of days in the month of the contract date, then the number of days in the all the months between. Subtract the number of days in the current month of today from the days in the month of today (so 1/10 would be 31-10 or 21) ADD that to the total of days in all the months between (Add Feb, March, April, etc) to get all the Month days, then ADD in the number of days in the in the Contract date month.(so 12/20 would be 20 days.) Then 21 days, plus 20 days, plus all the Month days.
If you want working days or only non holidays, you will need to do more calcs or use a project grid to do all that for you.
I am sorry this isn't elegant, I think with more time, it could be.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Hi
I took help from other community questions.
653 is calculated based on the below formulae to "1 Year 9 Months and 18 days"
=INT([Days to Contract Renewal]@row / 365) + " Years " + INT(([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365) / 30) + " Months " + ([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365 - INT(([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365) / 30) * 30) + " Days"
How do I covert this to "21 months and 18 days"
Thanks!
-
Hi.
If you are getting the value of 653, and you are using 30 days to 'gauge' as a 'month', then you only need to divide 653 by 30 and you get 21.7666666 recurring which would round up to .8 giving you 21.8 OR 21 months and 8 days.
Will that work for you?
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Thank you. Can you please rewrite this formulae to read only months and days. Currently it says 1 Year 9 Months and 18 days
=INT([Days to Contract Renewal]@row / 365) + " Years " + INT(([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365) / 30) + " Months " + ([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365 - INT(([Days to Contract Renewal]@row - INT([Days to Contract Renewal]@row / 365) * 365) / 30) * 30) + " Days"
-
Hi
If [Days to Contract Renewal]@row = 653
Then
=INT([Days to Contract Renewal]@row / 30) + " Months " + ROUND((MOD([Days to Contract Renewal]@row / 30, 1) * 10), 0) + " Days"
You can split this up into two formulas in two columns if you want to have each number broken out.
I hope this helps.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!