function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jennifer Johnson 8Jennifer Johnson 8 

formula error

I have the below formula that has been working fine, until this month...

DATE( 
year(End_of_Mth__c) 
+ floor((month(End_of_Mth__c) + 4)/12) + if(and(month(End_of_Mth__c)=12,4>=12),-1,0) 
, 
if( mod( month(End_of_Mth__c) + 4 , 12 ) = 0, 12 , mod( month(End_of_Mth__c) + 4 , 12 )) 
, 
min( 
day(End_of_Mth__c), 
case( 
max( mod( month(End_of_Mth__c) + 4 , 12 ) , 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) 
) 
)

when my End of Mth date is 8/31/2014 the date being returned is 12/31/2015 instead of 12/31/2016.  All other months are working fine (before and after 8/31/2014) - I can't figure out why just that date is messing up?

Thank you!!
Best Answer chosen by Jennifer Johnson 8
Suneel#8Suneel#8
Can you try below formula

DATE( 
year(SLAExpirationDate__c) 
+if(month(SLAExpirationDate__c)>8,1,0) 
, 
if( mod( month(SLAExpirationDate__c) + 4 , 12 ) = 0, 12 , mod( month(SLAExpirationDate__c) + 4 , 12 )) 
, 
min( 
day(SLAExpirationDate__c), 
case( 
max( mod( month(SLAExpirationDate__c) + 4 , 12 ) , 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) 
) 
)

All Answers

Suneel#8Suneel#8
Below is the evaluation of your formula and it looks like 12/31/2015 is the right calculation(2014 +1+ 0).Please check
year(End_of_Mth__c) =2014
+ floor((month(End_of_Mth__c) + 4)/12)  =(8+4)/12=1
+ if(and(month(End_of_Mth__c)=12,4>=12),-1,0) =if( AND(8=12, 4>=12),-1,0))=0 Inner AND expression is always false as 4>=12 is always false and would take 0 as the value.

Kindly mark this question as solved if your query is resolved
Jennifer Johnson 8Jennifer Johnson 8
I'm sorry, I'm not great at these formulas - someone else wrote the above.  I'm looking for a field called Fourth Month to return a date that is 4 months after my field End of Mth.  So when the End of Mth field date is 8/31/2014 my Fourth Month field should be 12/31/2014 not 2015. 
Every other date in the End of Mth field is returning the correct Fourth Month date - only End of Mth = 8/31/2014 is incorrect.

thank you
Suneel#8Suneel#8
Can you try below formula

DATE( 
year(SLAExpirationDate__c) 
+if(month(SLAExpirationDate__c)>8,1,0) 
, 
if( mod( month(SLAExpirationDate__c) + 4 , 12 ) = 0, 12 , mod( month(SLAExpirationDate__c) + 4 , 12 )) 
, 
min( 
day(SLAExpirationDate__c), 
case( 
max( mod( month(SLAExpirationDate__c) + 4 , 12 ) , 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) 
) 
)
This was selected as the best answer
Jennifer Johnson 8Jennifer Johnson 8
?THAT WORKED! Thank you so much!!