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
In a Formula Field;
I tried to add a month to the date and subtract some other date value as follows but the formula returns me "#Error!" in the UI
DATE( YEAR( reportDueDate__c ), ( MONTH( reportDueDate__c )+1 ) , DAY( reportDueDate__c) ) -
DATE( YEAR( reportDate__c ) , MONTH( reportDate__c ), DAY( reportDate__c) )
How could this adding 1 month cause an error? ( MONTH( reportDueDate__c )+1 )
Please help. Thanks in advance
There are two things you need to look at here. The documentation helps: https://help.salesforce.com/htviewhelpdoc?id=customize_functions.htm&siteLang=en_US#date_and_time_functinons
DATE()
Returns a date value from year, month, and day values you enter. Salesforce displays an error on the detail page if the value of the DATE function in a formula field is an invalid date, such as February 29 in a non-leap year.
MONTH()
Returns the month, a number between 1 (January) and 12 (December) in number format of a given date.
Because Month() returns a number, there's no part of your logic that ensures it's between 1 and 12. I'm assuming your error is occurring due to reportDueDate__c being December.
There are a number of common date formulas in the help too: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm Perhaps look at Adding Days, Months, and Years to a Date
To add a month, this worked for me:
DATE(YEAR(Today()), MONTH(Today()), DAY(Today())) + ((365 / 12))
I got this from the same link recommended by @Nick Cook's recommended URL: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm
