Adventures in Salesforce formulas & validation rules with WEEKDAY(), IF(), & INCLUDES()

Formulas are one of those tricks up an admin’s sleeve that are extremely powerful and cool (or maybe that’s just how formulas make me feel.) 🤔

It almost feels like coding and you can accomplish a lot with just a few lines of formulas in a field or validation rule.

Until recently, my experience with formulas had been pretty elementary but recent projects at work and Trailhead got my gears turning and I realized the endless possibilities of formulas.

In this post, I’m going to walk through a formula I recently built and list some resources that have really helped me as I’ve expanded my formula skills.


All changes in Salesforce require clear requirements and this one was no different. I work for a company that sells online advertising. We send email blasts, newsletters, and other marketing content that advertisers can sponsor. While it’s not physical, our inventory still has restraints. For example, we only send certain email blasts on Tuesdays and Thursdays.

My Ad Operations team who is responsible for trafficking ads noticed that sometimes sales reps would put the wrong date in for a product on a contract and that would cause confusion. If the product date was Wednesday but email blasts only go out on Tuesday or Thursday some back and forth would be required to determine the correct date.

Wouldn’t it be nice if depending on the product a sales rep entered they’d only be allowed to enter specific days of the week?


Validation rules and a complex formula to the rescue! If you’re new to validation rules, check out the Salesforce docs and this Trailhead module.

I wanted the the solution to be modified by an end user and flexible. Some of our products don’t have a specific send date so I couldn’t make a solution that requires a send date or else that would be a problem.

Example of the picklist on a product record.

I created a new multi-select picklist called “Product Send Days” and put that on the Product object. Multi-selects have their downsides since they can be terrible for reporting but for this use case it was perfect. With just a couple clicks our Ad Ops team could update the days of the week our products send.

Now it’s time to flip to the validation rule and the formula. The Opportunity Product object has a relationship to the Product object, which means I’d be able to reference it in my formula. It’s import to remember that when you’re working on a formula. If there is no relationship between the objects it won’t work.

Accessing my Product Send Days field by drilling down from Opportunity Product.

IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false, true)

So what does the above say? Let’s break it down.

The biggest problem that I immediately saw was that I needed some way to translate the day of the week on my Product object to the actual day of the week that was being chosen on the Opportunity Product. This is where the WEEKDAY() function comes into play.

WEEKDAY checks the ServiceDate to see if it’s equal to 1, which is Sunday. ServiceDate is a field on the Opportunity Product. The INCLUDES() is a function unique to multi-select which checks to see if the multi-select has the value you want. So it’s taking my field Product2.Send_Days__c and checking to see if Sunday is selected.

We want something to happen when we ask the question “Does the ServiceDate equal Sunday and does Product2.Send_Days__c include Sunday?” So we are gonna wrap it in an IF() statement!

IF(logical_test, value_if_true, value_if_false)

We already know our logical test, that’s the WEEKDAY() and INCLUDES() where we’re checking to see if Sunday = Sunday. But here is where it gets tricky. For value_if_true we’re putting false because we don’t want the validation rule to fire. 😳

If the sales rep is entering the date correctly, we don’t want to stop them! So only if Sunday ≠ Sunday do we want it to fire. Note: If you put null instead of false in the 3rd position, the validation rule would not fire.

So now that we’ve broken down how to create it for Sunday, you just need to copy & paste to make it for the other 6 days of the week, like so:

IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false,
IF(WEEKDAY(ServiceDate)==2 &&
INCLUDES(Product2.Send_Days__c,'Monday'),false,
IF(WEEKDAY(ServiceDate)==3 && INCLUDES(Product2.Send_Days__c,'Tuesday'),false,
IF(WEEKDAY(ServiceDate)==4 && INCLUDES(Product2.Send_Days__c,'Wednesday'),false,
IF(WEEKDAY(ServiceDate)==5 && INCLUDES(Product2.Send_Days__c,'Thursday'),false,
IF(WEEKDAY(ServiceDate)==6 && INCLUDES(Product2.Send_Days__c,'Friday'),false,
IF(WEEKDAY(ServiceDate)==7 && INCLUDES(Product2.Send_Days__c,'Saturday'),false,
TRUE
)))))))

I didn’t talk about the double ampersands (&&) but that’s just a logical operator for AND. You could switch out && for AND and it would work just as well.

I thought this formula was looking pretty good…until I went to test it. Which is why you should always test before you release to your users. Remember when I said that some products didn’t have specific send days? Well in the formula above there is no place to allow for that. My validation rule is not going to let a user save an Opportunity Line without a correct send day.

So I added some extra logic at the very top.

NOT((ISBLANK(Product2.Send_Days__c)) || (ISBLANK(ServiceDate)))

Here I’m saying, “If the Send Days field is blank OR the ServiceDate field is blank, you can ignore and not fire.” I had to wrap it in a NOT() because I only want the validation rule to keep working if the fields aren’t blank.

Similar to the double ampersands (&&) the double pipe (||) means OR. You can use either!

So here is the final formula in all its glory. ✨

NOT((ISBLANK(Product2.Send_Days__c)) || (ISBLANK(ServiceDate)))
&&
IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false,
IF(WEEKDAY(ServiceDate)==2 &&
INCLUDES(Product2.Send_Days__c,'Monday'),false,
IF(WEEKDAY(ServiceDate)==3 && INCLUDES(Product2.Send_Days__c,'Tuesday'),false,
IF(WEEKDAY(ServiceDate)==4 && INCLUDES(Product2.Send_Days__c,'Wednesday'),false,
IF(WEEKDAY(ServiceDate)==5 && INCLUDES(Product2.Send_Days__c,'Thursday'),false,
IF(WEEKDAY(ServiceDate)==6 && INCLUDES(Product2.Send_Days__c,'Friday'),false,
IF(WEEKDAY(ServiceDate)==7 && INCLUDES(Product2.Send_Days__c,'Saturday'),false,
TRUE
)))))))

As promised, here are some resources that have helped me a ton in my formula journey:

Shout out to Natalya Murphy who sat with me at WIT DC’s #SalesforceSaturday to work on this with me. ☁️


Let me know in the comments if you’ve tried a similar formula or if you would have done it differently!

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 )

Connecting to %s