Formula Help: Creating a formula field Customer Status off a Date field - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Rebecca (Becky) SweetmanRebecca (Becky) Sweetman 

Formula Help: Creating a formula field Customer Status off a Date field

I am attempting to use a Date field called Last Posting Date to push a text value of Active to a text formula field if date within 12months of today. From a dev site I found this formula below which works - apart from the fact that I see 2017 dates like this: 12/07/2017 as showing as "active" also. If blank, it is showing as "unknown" fine. Any ideas to troubleshoot? Thanks!
IF(
(
12*(YEAR(Last_Posting_Date__c) - YEAR(TODAY())) +
(MONTH(Last_Posting_Date__c) - MONTH(TODAY()))
) <= 12,
"Active",
"Unknown"
)
Best Answer chosen by Rebecca (Becky) Sweetman
Eric PraudEric Praud
Hi Rebecca,

For the second one, I was missing something in the ISBLANK function, and I needed to replace < with > (apologies, still early...):
IF(ISBLANK(Last_Posting_Date__c), NULL,
IF(ADDMONTHS(Last_Posting_Date__c,12)>=TODAY(), "Active","Unknown"))

All Answers

Eric PraudEric Praud
Hi Rebecca,

Try this:
IF(ADDMONTHS(Last_Posting_Date__c,12)<=TODAY(), "Active","Unknown")

 
Eric PraudEric Praud
If you don't want to see anything when the date's blank:
IF(ISBLANK(), NULL,
IF(ADDMONTHS(Last_Posting_Date__c,12)<=TODAY(), "Active","Unknown"))

 
Amnon KruviAmnon Kruvi
Hi Becky, 

There is no need to get this complicated. You can use the newer ADDMONTHS function:
IF (Last_Posting_Date__c > ADDMONTHS(TODAY(), - 12, "Active", null) 
Rebecca (Becky) SweetmanRebecca (Becky) Sweetman
Hi Eric - the top one didn't work. For some reason it didn't take into account the latest year's dates - but had those over 12months as active. The second one gave me a syntax error  'ISBLANK()'. Expected 1, received 0. although the idea is great and I do want to leave it blank if date's blank.
Arnaud I also got a syntax error from  - Missing ')'
Amnon KruviAmnon Kruvi
Oh, sorry - forgot to close the bracket. Writing formulas on a phone is always fun :) 
​​​​​​IF (Last_Posting_Date__c > ADDMONTHS(TODAY(), -12), "Active", null) 
Rebecca (Becky) SweetmanRebecca (Becky) Sweetman
So Eric, I switched your one above: IF(ADDMONTHS(Last_Posting_Date__c,12)>=TODAY(), "Active","Unknown") which seemed to work - but how can I add the blank value if blank to that? Thanks for both your help!
Eric PraudEric Praud
Hi Rebecca,

For the second one, I was missing something in the ISBLANK function, and I needed to replace < with > (apologies, still early...):
IF(ISBLANK(Last_Posting_Date__c), NULL,
IF(ADDMONTHS(Last_Posting_Date__c,12)>=TODAY(), "Active","Unknown"))
This was selected as the best answer
Rebecca (Becky) SweetmanRebecca (Becky) Sweetman
BOOM! Thanks Eric and Amnon - love this community saving me hours of research and testing! :)