Convert count of days to months

Gaurav Chauhan

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!

Kevin Smith

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.

Kevin Smith

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.

Gaurav Chauhan

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!

Kevin Smith

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?

Gaurav Chauhan

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"

Kevin Smith

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.