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
Audrey GarciaAudrey Garcia 

Formula field incorrectly adding a year to some dates

The Target Date Field = The last monthly Contact date with a month added, but does not exceed 5 days from the last day of the target month(the month after the last contact date). Ex: Last Monthly Contact date is January 27th, the Target Date would be February 23rd. Another field, Month after date report received adds a month and does not exceed the next month. Example: Date Report Received is January 27th, the Month After field would be February 27th. Same formula, I just excluded the 5 day part. I had to use both of these to end up with a date that says, If the Last Monthly Contact date is blank, populate the Target date field with the date from the Month After Date Report Received field. If the Last Monthly contact date field is populated, add a month, but don't let the date exceed 5 days from the last day of the target month. The Target Date formula was too large, so I put it in a field update. The issue is that some dates are adding a year. Example: Last contact date 11/15/16, Target Date 12/15/17. Why? What am I missing? Thank you SOOOO much in advance. :)

Month After Date Report Received Formula Field:
(DATE( 
year( Date_Report_Received__c ) 
+ floor((month(Date_Report_Received__c ) + 1)/12) + if(and(month(Date_Report_Received__c )=12,1>=12),-1,0) 

if( mod( month(Date_Report_Received__c ) + 1 , 12 ) = 0, 12 , mod( month(Date_Report_Received__c ) + 1 , 12 )) 

min( 
day(Date_Report_Received__c ), 
case( 
max( mod( month(Date_Report_Received__c ) + 1 , 12 ), 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) -5 

))

Field update to Update Monthly Contact Target Date Field:
IF(ISBLANK(Last_Monthly_Contact_Date__c ),Month_After_Date_Report_Received__c, 

(DATE( 
year(Last_Monthly_Contact_Date__c) 
+ floor((month(Last_Monthly_Contact_Date__c) + 1)/12) + if(and(month(Last_Monthly_Contact_Date__c)=12,1>=12),-1,0) 

if( mod( month(Last_Monthly_Contact_Date__c) + 1 , 12 ) = 0, 12 , mod( month(Last_Monthly_Contact_Date__c) + 1 , 12 )) 

min( 
day(Last_Monthly_Contact_Date__c), 
case( 
max( mod( month(Last_Monthly_Contact_Date__c) + 1 , 12 ), 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) -5 

)))
Audrey GarciaAudrey Garcia
I wanted to add that the only time I've found this happening, is when the Last Monthly Contact date is November 2016. Then the target date ends up being December 2017. Thank you.