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

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.

Step 6: Choose the Formula as the field type.

Step 7: Click Next to proceed.

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.

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.

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.

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.

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.

Step 2: Click on the Setup option.

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.

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

Step 6: Select the Formula as the field type.

Step 7: Click Next to proceed.

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.

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.

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.

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.

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

You may also like to read:

• Salesforce Formula Field to Check Null Values for Dates
• Salesforce Date Formula Business Days

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