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 C
    Aug 7, 2015 at 2:00
  • @NickCook its a Date field
    – Hasantha
    Aug 7, 2015 at 2:05
  • And the value of that date field?
    – Nick C
    Aug 7, 2015 at 2:07
  • @NickCook the value is "2015-08-31"
    – Hasantha
    Aug 7, 2015 at 2:10

2 Answers 2


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, 2015 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, 2016 at 8:31

You must log in to answer this question.

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