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)))
  • 1
    is MSA_Expiration_Date__c a formula field? – battery.cord Aug 12 '19 at 19:45
  • No, it is an editable date field. – Rory Aug 12 '19 at 20:26
  • What is the return type of the Formula? – Thomas Taylor Aug 12 '19 at 21:21
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.

| improve this answer | |
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)))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.