Salesforce Date Range Formula : Bijay Kumar
by: Bijay Kumar
blow post content copied from SalesForce FAQs
click here to view original post
### Summary of Salesforce Date Range Formula In this content, the author discusses how to use the Date Range Formula in Salesforce to track opportunities created within the last 30 days. The tutorial explains the steps to create advanced formulas using date fields to check if specific dates fall within defined ranges. ### Key Details: - **Purpose**: To find opportunities created in the last 30 days using Salesforce. - **Date Field**: Salesforce uses the Date data type to store dates. - **Functions Used**: The tutorial highlights the use of logical functions like `AND`, `OR`, and `ISPICKVAL` to construct formulas. - **Example Formula**: An example formula checks if the Lead Status is "Open - Not Contacted" and if the Start Date falls within the previous month: ```plaintext 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", "") ``` - **Steps to Create Formula in Salesforce Lightning**: 1. Access Salesforce Setup. 2. Navigate to Object Manager and select the desired object (e.g., Product). 3. Create a new field and select Formula as the type. 4. Input the formula checking the date range. - **Delivery Charge Example**: Another example provided calculates delivery charges based on the payment date relative to the delivery date. ### Conclusion The tutorial effectively outlines how users can create advanced formulas in Salesforce to check if certain dates fall within a specified range, making it easier to track and manage opportunities and related activities. ### Additional Information: Understanding how to use date range formulas can significantly improve efficiency in Salesforce, especially for sales managers and teams tracking various deadlines and statuses. ### Relevant Hashtags for SEO: #Salesforce #DateRangeFormula #SalesforceTutorial #SalesforceLightning #CRM #SalesManagement #DataTracking #SalesforceFormulas #LeadManagement #SalesforceTips
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.
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.
- Choose the Formula as the field type. Click Next to proceed.
- 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.
- 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.
- Select the Field Level Security to make the field visible to the profiles.
- Select the Page Layout Name for which the formula field is to be added. Click Save to save the formula field.
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.
As a result, we now know how to utilize Salesforce Lightning’s advanced formula to determine if the date falls within the specified range.
Conclusion
We have learned the advanced Salesforce formula to check if a date falls within a specified range using Salesforce.
Moreover, we have learned that Salesforce Lightning offers a straightforward method to determine if a date falls within a specific range.
You may also like to read:
- Salesforce Formula Field to Check Null Values for Dates
- Salesforce Date Formula Business Days
- Extract Year From Date Using the YEAR() Function in Salesforce
- UPPER and LOWER Functions in Salesforce
- Add Months to Date in Salesforce using addmonths() function
The post Salesforce Date Range Formula appeared first on SalesForce FAQs.
November 20, 2025 at 04:30PM
Click here for more details...
=============================
The original post is available in SalesForce FAQs by Bijay Kumar
this post has been published as it is through automation. Automation script brings all the top bloggers post under a single umbrella.
The purpose of this blog, Follow the top Salesforce bloggers and collect all blogs in a single place through automation.
============================
Post a Comment