1

I am trying to check the syntax of the following formula field but it is not displaying any information. When I try to save, it says there are error and to read the message below. When I scroll down there is still no error message.

I checked the parenthesis and everything seems fine. Does anyone know why this is not saving correctly? Or better yet, why the check syntax button is not working?

IF(((MONTH(MSA_Expiration_Date__c)+3)>12),

CASE(12-MONTH(MSA_Expiration_Date__c)+3, 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None") + " " + TEXT(DAY(MSA_Expiration_Date__c))+", "+ TEXT(YEAR(MSA_Expiration_Date__c)+1), 

CASE(MONTH(MSA_Expiration_Date__c)+3, 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None") + " " + TEXT(DAY(MSA_Expiration_Date__c))+", "+TEXT(YEAR(MSA_Expiration_Date__c)))
3
  • 1
    is MSA_Expiration_Date__c a formula field? Aug 12, 2019 at 19:45
  • No, it is an editable date field.
    – Rory
    Aug 12, 2019 at 20:26
  • What is the return type of the Formula? Aug 12, 2019 at 21:21

2 Answers 2

1

Like Peter, this works for me in a formula returning Text. However, I think there's a logic error -- October dates for MSA_Expiration_Date__c return May, November returns April, and December returns March. You'd want to add 3 & subtract 12 (net, subtract 9) to get the month three ahead of the MSA_Expiration_Date's. If that's what you want, the text representation of the date 3 months in the future, you can simplify this a great deal:

CASE(MONTH(ADDMONTHS(MSA_Expiration_Date__c,3)), 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None") + 
" " + TEXT(DAY(ADDMONTHS(MSA_Expiration_Date__c,3))) 
+ ", " + TEXT(YEAR(ADDMONTHS(MSA_Expiration_Date__c,3)))

Using ADDMONTHS() on the day as well as the month and year makes sure you're covering month-end scenarios and not returning things like February 30, 2020 for an MSA_Expiration_Date__c of 2019-11-30.

2

the code worked for me, Is the formula field set to text?

I tested the following with a standard field

IF(
    ((MONTH(LastActivityDate)+3)>12),
CASE(MONTH(LastActivityDate)+3, 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None") + " " + TEXT(DAY(LastActivityDate))+", "+ TEXT(YEAR(LastActivityDate)+1), 
CASE(MONTH(LastActivityDate)+3, 
1, "January", 
2, "February", 
3, "March", 
4, "April", 
5, "May", 
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December", 
"None") + " " + TEXT(DAY(LastActivityDate))+", "+TEXT(YEAR(LastActivityDate)))

You must log in to answer this question.

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