Salesforce Date Range Formula

While working for an organization that uses Salesforce, I was assigned a task to find all opportunities that were created in the last 30 days. So, to fulfill this requirement, I define an advanced formula that checks the date range.

Using a Date Range Formula, the user can automatically check if the opportunity creation date is within the last 30 days. In this Salesforce tutorial, we will learn the Salesforce Date Range Formula.

Table of Contents

How to Use Date Range Formula in Salesforce

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

The Date field is the Salesforce data type 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.

We want to verify that the Lead Status is “Open – Not Contacted“. If the Start Date is between the first day of the previous month and the last day of the previous month, the Formula returns the text “Lead Opened Last Month“.

So, for this, we create an advanced 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 proceed to learn how to check if a date falls within a specific range using Salesforce Lightning.

Check if the Date Falls Within Range Using Salesforce

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

Click the Setup icon in the top right corner of the page to access Salesforce Setup.
- Click Object Manager from the menu’s left side.

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

Choose Fields & Relationships. Next, select New.

Check if Date falls within range using Salesforce Lightning

Choose the Formula as the field type. Click Next to proceed.

Check if Date falls within range using Salesforce Lightning Example

After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I enter the Field Label as Delivery Charges.
- 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

Enter the formula that checks if the date falls within the range. In the Advanced 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 now 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 to check 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 to check 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.

To verify that the formula is valid, click Check Syntax. We can specify how you want to handle the empty field and include the Description and Help Text as alternatives. Then, select Next.

Check if Date falls within range in Salesforce Lightning Example

Select the Field Level Security to make the field visible to the profiles.

How to check if date falls within range using Salesforce Lightning

Select the Page Layout Name for which the formula field is to be added. Click Save to save the formula field.

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

We can use the formula field once it has been created. 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 the Delivery Charges field appears, which checks if the date falls within the specified range and displays the result.