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

  • What is the reportDueDate__c value that is causing this?
    – Nick Cook
    Aug 7 '15 at 2:00
  • @NickCook its a Date field
    – Hasantha
    Aug 7 '15 at 2:05
  • And the value of that date field?
    – Nick Cook
    Aug 7 '15 at 2:07
  • @NickCook the value is "2015-08-31"
    – Hasantha
    Aug 7 '15 at 2:10

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


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.


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

  • Thanks @Nick Cook I was able to get rid from #Error! with ensuring the month value is between 1 and 12, if( (MONTH( reportDueDate__c )+1) < 12 , expression1, expression2)
    – Hasantha
    Aug 7 '15 at 4:11

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

  • This one is nice but it doesn’t always return an exact date, sometimes it may vary for few days. Jun 3 '16 at 8:31

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.