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.

5
  • Can you just not put Extension __c within the Month(Renewal_Date)? Nov 17, 2017 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, 2017 at 12:48
  • And Extension is an Integer? Nov 17, 2017 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, 2017 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, 2017 at 13:25

1 Answer 1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .