function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ecastilloecastillo 

Date Formula failing for results greater than 02/28/2014

We have a workflow that adds 7 months to our opportunity close date. The formula has been in place for a little over 2 years now. But it seems that once the resulting date is > 02/28/2014, we now get errors during conversion stating we're inserting a invalid date (I've had to turn off this workflow).

 

I've also created a stand alone formula field to test the formula and I'm getting the #Error! Message in the field.

 

Anyone else having issues with formulas that calculate past 02/28/2014 (Leap year....).

 

Formulas I've tried:

 

1.

IF(MONTH(TODAY())< 7,DATE(MONTH(TODAY())+7,DAY(TODAY()),YEAR(TODAY())),DATE(MONTH(TODAY())+7,DAY(TODAY()),YEAR(TODAY())+1))

2.

DATE(YEAR( Date_Created__c) , MONTH(Date_Created__c) + 7 , DAY(Date_Created__c) )

 

3.

IF(Month(TODAY())<7,(DATE(YEAR(TODAY()), 
MONTH(TODAY())+7,DAY(TODAY()))), 
(DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 5,DAY(TODAY()))))

 

4.

DATE( YEAR(TODAY()) , (MONTH(TODAY()) + 7), DAY(TODAY()))

 

What does work is CreatedDate + 212 (or any number greater than 7 months) which gets us close, but doesn't meet the business requirement.

 

Thanks.

 

 

 

 

puneet28puneet28

Hi ecastillo,

There are some really helpful formulas here:

https://success.salesforce.com/ideaview?id=08730000000BrQ2AAK

 

Try using them as per your need.

They worked for me :)

jayjaysjayjays

Hi,

 

2014 is not a Leap Year, the next Leap Year is 2016.  The issue you are facing is that you are trying to add 7 months to a date to the day.  Your formulas are looking for the same day in Feb. Therefore, 29/07/2013 becomes 29/02/2013, 30/02/2013 becomes 30/07/2013 and 31/07/2013 becomes 31/02/2013. These are not valid dates in the calendar. It is fine for all other months. You need to decide what the date should be for these days, maybe the 1st March for these?

 

IF(MONTH(TODAY())=7 && DAY(TODAY())>IF(MOD(YEAR(TODAY()),4)=0,29,28), 
DATE(YEAR(TODAY()),3,1), 
IF(MONTH(TODAY())< 6,DATE(YEAR(TODAY()),MONTH(TODAY())+7,DAY(TODAY())),DATE(YEAR(TODAY())+1,MONTH(TODAY())-5,DAY(TODAY()))) 
)

 

This will work for Leap years and 29th too.

 

Thanks,

James.