 ShowAll Questionssorted byDate Posted Jennifer 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#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

) Suneel#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 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#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 8
?THAT WORKED! Thank you so much!!