Tips for using formulas effectively:

  • Write it out in plain English first
  • Look out for keywords such as AND or IF. These functions are building blocks of larger, more complicated formulas

You can use $Profile.Name to check the user’s profile. For example.

Use the below formula to ensure that opportunities marked as closed lost can’t be saved without providing a reason unless it’s a system administrator. It’s a validation rule.

AND(
  $Profile.Name <> "System Administrator",
  ISCHANGED(StageName),
  ISPICKVAL(StageName, "Closed Lost"),
  ISPICKVAL(Loss_Reason__c, "")
)

The following formula returns hot if it’s from USA and is >$1,000,000 and is partner referral. It’s Warm if it’s purchased list or web. Otherwise, it’s cold.

IF(
  AnnualRevenue > 1000000 &&
  CONTAINS(CASE(Country, "United States", "US", "America", "US", 
    "USA", "US", "US", "US", "NA"), "US"),
  IF(
    ISPICKVAL(LeadSource, "Partner Referral"), "Hot",
    IF(
      ISPICKVAL(LeadSource, "PurchasedList") ||
      ISPICKVAL(LeadSource, "Web"),
      "Warm", "Cold"
    )
  ), "Cold"
)

The following formula returns true if an opportunity’s Close Date is not in the current month:

OR (
CloseDate < DATE( YEAR(TODAY()), MONTH(TODAY()), 1),  IF ( AND ( MONTH (TODAY() ) =1,  CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, 
IF (
AND (
MONTH (TODAY() ) =2, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 28)), 
true,
IF (
AND (
MONTH (TODAY() ) =3, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)), 
true,
IF (
AND (
MONTH (TODAY() ) =4, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =5, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, 
IF (
AND (
MONTH (TODAY() ) =6, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =7, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =8, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =9, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =10, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =11, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =12, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, false
)))))))))))))

This determines whether the close date is not in this month:

NOT( 
  AND( 
    MONTH( CloseDate ) = MONTH( TODAY() ), 
    YEAR( CloseDate ) = YEAR( TODAY() ) 
  ) 
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s