function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
OfficegnomeOfficegnome 

IF Formula help with blank date

I'm just started as Admin for an org that has a lot of complicated formulas implemented -- I want to generally overhaul them but in the meantime I have to work with what I've got...

 

There's an existing formula I need to tweak to have an additional results, and I'm having trouble figuring out what to do.... HELP!  

 

Here is the existing formula:

 

IF( Subscription_Purchases__c > 0 && Expiration_Date__c < TODAY(), "Expired",
IF( Subscription_Purchases__c > 0, "Purchased" ,
IF( Report_Pack_Purchases__c > 0, "Purchased", null)
))

 

I want to change the first IF statement to accomodate Cancelled accounts.  it should be something like:

If field Subscription_Purchases__c greater than 0, AND Expiration_Date__c greater than TODAY(), AND  field "CancellationDateAccount__c" is blank  --- return value of  "Expired",

(I know this is wrong way to write formula, just writing it out theoretically.  Expiration_Date and CancellationDateAccount are both date fields).

 

And then I want to also add an additional IF Statement to return "Cancelled" for those accounts I want to mark as such.

Essentially the ones that fall into this criteria:

If field "CancellationDateAccount__c" less than than or equal to Expiration_Date__c  --- return value of  "Cancelled",

 

Thanks in advance for anyone's help!

 

Best Answer chosen by Admin (Salesforce Developers) 
Prafull G.Prafull G.

IF(Subscription_Purchases__c > 0 && Expiration_Date__c < TODAY() && ISBLANK(CancellationDateAccount__c),  "Expired",
IF(Subscription_Purchases__c > 0, "Purchased" ,
IF( Report_Pack_Purchases__c > 0,  "Purchased",
IF(CancellationDateAccount__c <= Expiration_Date__c, "Cancelled",
null))))

All Answers

Prady01Prady01
IF( Subscription_Purchases__c > 0 && Expiration_Date__c < TODAY() && if(ISBLANK(CancellationDateAccount__c),1,0), "Expired",
IF( Subscription_Purchases__c > 0, "Purchased" ,
IF( Report_Pack_Purchases__c > 0, "Purchased", 
IF(CancellationDateAccount__c <= Expiration_Date__c,"Cancelled" , null
	))))

 Hi there, Hope this helps!

  Prady

 
OfficegnomeOfficegnome

Thanks for response -- tried that and got this error;

 

 Error: Incorrect parameter type for function 'and()'. Expected Boolean, received Number 

 

looks like it is in relation to the top first expression...
Prafull G.Prafull G.

IF(Subscription_Purchases__c > 0 && Expiration_Date__c < TODAY() && ISBLANK(CancellationDateAccount__c),  "Expired",
IF(Subscription_Purchases__c > 0, "Purchased" ,
IF( Report_Pack_Purchases__c > 0,  "Purchased",
IF(CancellationDateAccount__c <= Expiration_Date__c, "Cancelled",
null))))

This was selected as the best answer
Prady01Prady01
IF( Subscription_Purchases__c > 0 && Expiration_Date__c < TODAY() && if(ISBLANK(CancellationDateAccount__c),true,false), "Expired",
IF( Subscription_Purchases__c > 0, "Purchased" ,
IF( Report_Pack_Purchases__c > 0, "Purchased", 
IF(CancellationDateAccount__c <= Expiration_Date__c,"Cancelled" , null
	))))

 Give this a try! thanks.

    Prady
OfficegnomeOfficegnome

Thanks everyone - working now!