Skip to main content
added doc link
Source Link
Thomas Taylor
  • 5.9k
  • 25
  • 45

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()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.

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.

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.

Source Link
Thomas Taylor
  • 5.9k
  • 25
  • 45

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.