0

I have created a formula field Date__c. In this date field, I want to calculate the following:

Renewal_Date__c + 1year + Extension__c

The value entered in Extension__c is in months.

So, I have the following formula:

DATE(  (YEAR( Renewal_Date__c )+1), (MONTH( Renewal_Date__c )+ Extension__c ) ,DAY( Renewal_Date__c))

The formula works fine if I leave out the +Extension__c part. How can I make it work so that I can add the number of months as well?

Tia, Lily.

|improve this question
  • Can you just not put Extension __c within the Month(Renewal_Date)? – Koen Certyn Nov 17 '17 at 12:46
  • If I change the formula to: DATE( (YEAR( Renewal_Date__c )+1), (MONTH( Renewal_Date__c + Extension__c )) ,DAY( Renewal_Date__c)), the month value is simply ignored. – Lily Nov 17 '17 at 12:48
  • And Extension is an Integer? – Koen Certyn Nov 17 '17 at 12:49
  • Yes, Data Type = Number. I tried hardcoding something, and that also gave me an error. When I write: DATE( (YEAR( Renewal_Date__c )+1), (MONTH( Renewal_Date__c )+ 9 ) ,DAY( Renewal_Date__c)), I would expect to add an additional 9 months, but it seems this is not the case. I get an error in the field instead. Maybe I am using the months calculation incorrectly... – Lily Nov 17 '17 at 12:53
  • What's the value in Renewal_Date__c? If the value of that field and the Extension__c field add up to more than 12 it will give an error obviously. – Folkert Nov 17 '17 at 13:25
0

If you want to add time to a Date field, you need to do that in days. So start with the Renewal Date, then add 1 year (365 days) and any other days from the Extension.

Renewal_Date__c + 365 + ( ( 365 / 12 ) * Extension__c )

Because months can vary in length, you need to use the approximation of a month (365 days divided by 12) and multiply that with the extension.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.