Salesforce Date Range Formula

In this Salesforce tutorial, we will learn the Salesforce Date Range Formula. Additionally, we will go over how to use Salesforce Lightning and Salesforce Classic to check if a date falls within a specific range.

While working for an organization that uses Salesforce, I was assigned a job to set the status for opportunities that were closed within 7 days. So, to fulfill this requirement, I define an advanced formula that checks the date range.

If you want to learn how to check if a date falls within a specific range using Salesforce Lightning and Salesforce Classic, you have come to the right place.

The following are the topics that we will cover in this salesforce tutorial:

Salesforce Date Range Formula
Check if Date falls within range using Salesforce Lightning
Check if Date falls within range using Salesforce Classic

Table of Contents

Salesforce Date Range Formula

In Salesforce, we can track different activities, such as renewal dates, delivery dates, birthdays, conversion dates, etc., and for all such activities, we use date fields.

The date field is the Salesforce data type that is used to store and record dates. So, to check if a date falls within a specific range, we need to define the date range formulas in Salesforce.

To check if a date falls within a specific range, we utilized the AND and OR functions with comparison operators.

Let’s understand this concept with the help of an example:

Suppose we are working as a sales manager, and we are responsible for monitoring leads.

And we want to check if the Lead Status is “Open – Not Contacted”. If the Start Date is between the first day of the previous month to the last day of the previous month, the Formula Returns the text “Lead Opened Last Month”.

So, for this, we create an advance formula given below:

IF(AND(ISPICKVAL(LeadStatus, "Open - Not Contacted"), StartDate >= DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), StartDate < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), "Lead Opened Last Month", "")

With this, we now know about the Salesforce Date Range Formula. Next, we will move ahead and learn how to check if a date falls within a specific range using Salesforce Lightning.

Read: Salesforce Formula Date Less Than Today

Check if Date falls within range using Salesforce Lightning

Here, are the steps to define the Salesforce date range formula in Salesforce Lightning:

Step 1: Click the Setup icon in the top right corner of the page to access Salesforce Setup.

Step 2: Click Object Manager from the menu’s left side.

Step 3: Look for the item to which we want to check if the date falls within the range. I choose the Product object from the list of objects in this instance.

Step 4: Choose Fields & Relationships.

Step 5: Next, select New.

Check if Date falls within range using Salesforce Lightning — Check if the Date falls within range using Salesforce Lightning

Step 6: Choose the Formula as the field type.

Step 7: Click Next to proceed.

Check if Date falls within range using Salesforce Lightning Example — Check if the Date falls within range using Salesforce Lightning Example

Step 8: After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I enter Field Label as Delivery Charges.

Step 9: Select Currency as the Formula Return Type. Here, I also set its decimal place to 0. After that, click Next.

Check if Date falls within range in Salesforce Lightning — Check if the Date falls within the range in Salesforce Lightning

Step 10: Enter the formula that checks if the date falls within the range. in the Advance Formula editor. Here, is the formula:

IF(
AND(
ISPICKVAL( Delivery_Tracking__c , "Delivered"),
Payment_Date__c <= Delivery_Date__c + 7,
Payment_Date__c >= Delivery_Date__c
),
0,
100
)

This formula determines the delivery charge for an order based on three criteria which are as follows:
- The delivery tracking status is set to Delivered.
- The payment date is on or after the delivery date.
- The payment date is within 7 days after the delivery date.
Firstly, we use the IF() conditional function to determine whether these three conditions are true or false and return a value based on the result.
Next, we use the logical function AND() which returns true if each of the defined requirements is satisfied.
Then, we use the ISPICKVAL() function, which determines whether the picklist field for Delivery_Tracking__c includes the value “Delivered”.
Next, we define the date range that checks if the payment date is on or before 7 days after the delivery date “Payment_Date__c <= Delivery_Date__c + 7”.
After this, we define the date range that checks if the payment date is on or after the delivery date “Payment_Date__c >= Delivery_Date__c”.
If all three conditions are met, the formula returns a delivery charge of 0. Otherwise, it returns a delivery charge of 100.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text as alternatives.

Step 13: Then, select Next.

Step 14: Select the appropriate field Level Security and to make it visible to everyone, here we select the Visible checkbox.

Step 15: Select Next.

How to check if date falls within range using Salesforce Lightning — Check if the date falls within range using Salesforce Lightning

Step 16: Select the Page Layout Name for which the formula field is to be added.

Step 17: Click Save to save the formula field.

How to check if Date falls within range using Salesforce Lightning Example — Check if the Date falls within range using Salesforce Lightning Example

We can use the formula field once we’ve created it. Let’s look at an example:

Create a Product with the Delivery Date, Payment Date, and Delivery Tracking fields by opening the Products item and saving it.
After that, click on the Details section, and here the Delivery Charges field appears which checks if the date falls within the range and shows the result.

Salesforce Example Date Range Formula — Date Range Formula when Payment Date and Delivery Date are equal.

Salesforce Lightning Date Range Formula Example — Date Range Formula Example when Payment Date is less than Delivery Date.

Salesforce Lightning Date Range Formula — Date Range Formula when Payment Date is within 7 days of the Delivery Date.

As a result, we now know how to use Salesforce Lightning’s advance formula to check if the date falls within the range. Now, we’ll look at how to use the advance formula in Salesforce Classic to check if Date falls within the range.

Check if Date falls within range using Salesforce Classic

Here, are the steps to define the Salesforce date range formula in Salesforce Classic:

Step 1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and then click Switch to Salesforce Classic.

Check if Date falls within range using Salesforce Classic — Switch to Salesforce Classic

Step 2: Click on the Setup option.

Check if Date falls within range using Salesforce Classic Example — Setup Date falls within range Example

Step 3: Scroll down to the Build section. Then under Customize, select the object for whom we want to check if the date falls within the range. Here, I choose the Orders from the object list.

Step 4: Click on the dropdown menu and select Fields.

Check if Date falls within range in Salesforce Classic — Check if the Date falls within the range in Salesforce Classic Order Object

Step 5: As you scroll down the page, click on the New button under Custom Fields & Relationships.

Check if Date falls within range in Salesforce Classic Example — Check if the Date falls within the range in Salesforce Classic Example

Step 6: Select the Formula as the field type.

Step 7: Click Next to proceed.

Date falls within range formula using Salesforce Classic — The date falls within the range formula using Salesforce Classic

Step 8: Enter a Field Label for the formula, and the Field Name will be automatically populated. Here, I enter Field Label as Discount.

Step 9: Select Text as the Formula Return Type. After that, click Next.

Date falls within range formula using Salesforce Classic Example — The date falls within the range formula using Salesforce Classic Example

Step 10: Enter the formula that checks if the date falls within the range. in the Advance Formula editor. Here, is the formula:

IF(
AND(
EffectiveDate >= DATE(YEAR(TODAY()), 1, 1),
EffectiveDate <= DATE(YEAR(TODAY()), 3, 15)
),
"Flat 50% off",
IF(
AND(
EffectiveDate >= DATE(YEAR(TODAY()), 8, 15),
EffectiveDate <= DATE(YEAR(TODAY()), 11, 30)
),
"Flat 20% off",
"No discount"
)
)

This formula returns a discount % based on whether the value of the EffectiveDate date field falls within one of two predetermined date ranges.
An IF() statement is used next, which checks whether the EffectiveDate is between January 1 and March 15 of the current year.
If this condition is true, the formula returns the text “Flat 50% off”.
The formula moves on to the second IF() expression if the first condition is not satisfied. The EffectiveDate is checked to see if it falls between August 15 and November 30 of the current year in this statement.
If this condition is true, the formula returns the text “Flat 20% off”.
The formula returns the text “No discount” if neither of the above conditions is satisfied.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text optionally.

Step 13: Then, click Next.

Step 14: Choose the appropriate Field’s Level Security, and then check the box Visible to make it visible to all.

Step 15: Click Next.

Salesforce Classic Date Range Formula Example

Step 16: Choose the Page Layout Name for which the formula field is to be added.

Step 17: Click Save to save the formula field.

Salesforce Classic check if Date falls within range — Salesforce Classic checks if the Date falls within the range

Once we’ve defined the formula field, we may use it. Let’s explore an example:

Create an order with the Order Start Date field value by opening the Orders tab and saving it.
In the details section, we will get the Discount field which shows the result based on the check if the date falls within the range.

How to check if Date falls within range in Salesforce Classic Example — When the Date falls within the range of January 1 and March 15 of the current year.

How to check if Date falls within range in Salesforce Classic — When the Date falls within the range except January 1 and March 15 of the current year and August 15 and November 30 of the current year.

Salesforce Classic check if Date falls within range Example — When the Date falls within the range of August 15 and November 30 of the current year.

We now understand the advanced formula to determine whether the date falls within a specific range or not in Salesforce Classic.

Conclusion

We have learned the advanced Salesforce formula to check if the date falls within the range using Salesforce.

Moreover, we have learned that both Salesforce Classic and Salesforce Lightning provide a simple procedure to determine how to check if a date falls within a specific range or not.

In addition to this, the following are the topics that we have discussed:

Salesforce Date Range Formula
Check if Date falls within range using Salesforce Lightning
Check if Date falls within range using Salesforce Classic

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.