Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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:48Data 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