In this Salesforce Tutorial, we will learn the formula to Calculate the Days Between Dates in Salesforce. Additionally, we will go over how to use Salesforce Lightning and Salesforce Classic to determine the days between dates.
I got a task at work that required me to calculate the days between dates using Salesforce. So, to fulfill this requirement, I define an advance formula for this.
If you want to learn How to get the days between dates 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:
- Formula for Number of Days Between Dates
- Formula to calculate Days between two Dates in Salesforce Lightning
- Formula to calculate Days between two Dates in Salesforce Classic
Formula for Number of Days Between Dates
In Salesforce, there are many scenarios where we may need to get the number of days between dates. For this, we will find the difference between two date values in the number format by subtracting one date from the other.
Here, is the general advance formula for the number of days between dates in Salesforce:
EndDateField - StartDateField
#OR
DATEVALUE(EndDateField) - DATEVALUE(StartDateField)Here, we subtract the StartDateField from the EndDateField to calculate the difference between these two dates.
In case, we have a Date and Time field and we want to calculate only the days between two Dates, we use the DATEVALUE function. This function converts the date and time values to the date value.
Let’s understand this with the help of an example:
Suppose, we want to calculate how many days it takes for a lead to convert into an opportunity. So, for this, we will find the number of days between the lead creation date and the lead conversion date in Salesforce.
With, this we have learned the Formula for the Number of Days Between Dates in Salesforce. Now, we will move further and see How to calculate Days between two Dates in Salesforce Lightning
Formula to calculate Days between two Dates in Salesforce Lightning
To calculate the number of days between two dates in Salesforce Lightning, the following are the steps that we use:
Step 1: Click the Setup icon in the top right corner of the page to access Salesforce Setup.
Step 2: Select Object Manager from the left side of the menu.
Step 3: Look for the object whose field you wish to get days between dates. In this instance, I choose the Orders object from the list of objects.
Step 4: Select 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 specify the field label as Delivery Days.
Step 9: Select the Formula Return Type as Number and also set the Decimal Places to Zero. After that, select Next.
Step 10: In the Advance Formula editor, enter the advance formula to determine the Days between two Dates. The formula is as follows:
Expected_Delivery_Date__c - EffectiveDate - In this example, we want to find out the number of days between the Expected Delivery Date and the Order Start Date.
- So, we subtract the EffectiveDate from the Expected_Delivery_Date__c fields.
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 optional fields.
Step 13: Then, select Next.
Step 14: Choose the appropriate field’s level of security, and then check the box beside visible to make it visible for everyone.
Step 15: Click, Next.
Step 16: Select the Page Layout Name for which the formula field is to be stated.
Step 17: Click Save to save the formula field.
Once we’ve set up the formula field, we may use it. Let’s examine an example:
- Open up the Orders Tab and create an order with Expected Delivery Date and Start Order Date and save it.
- After that, click on the Details section, and here is the Delivery Days field will appear that shows the difference between the two Dates.
As a result, we now understand how to apply an advanced formula in Salesforce Lightning to determine the days between two Dates. We’ll then examine how to use the advance formula in Salesforce Classic to calculate the difference between two dates in Days.
Read Salesforce Formula to get Last Day of Month
Formula to calculate Days between two Dates in Salesforce Classic
To calculate the number of days between two dates in Salesforce Classic, the following are the steps that we use:
Step 1: Open your Salesforce Classic edition from the Salesforce Lightning. Switch to Salesforce Classic by clicking on Avtar.
Step 2: Select Setup from the menu.
Step 3: Navigate to the Build section by scrolling down. Select the object for which we want to find the days between dates under Customize. I select Leads in this case from the list of objects.
Step 4: Select Fields from the dropdown menu by clicking on it.
Step 5: As we continue to scroll down the page, we will get the New button under Custom Fields & Relationships, click on it.
Step 6: Choose the Formula as the field type.
Step 7: To continue, click Next.
Step 8: After entering a Field Label for the formula, the Field Name will be filled in automatically. The Total Days is entered here as Field Label.
Step 9: Click Next, after selecting Number as the Formula Return Type. And, also set the Decimal Places to Zero.
Step 10: Fill out the advance formula editor with the formula to get the days between two days. The formula reads as follows:
DATEVALUE(End_Date_Time__c) - DATEVALUE(Start_Date_Time__c)- In this example, we want to find out the number of days between the Lead Start Date and the Lead End Date.
- So, we subtract the End_Date_Time__c from the Start_Date_Time__c fields. And we also use the DATEVALUE function to only get the date value from the date/time value.
Step 11: Click Check Syntax to make sure the formula is correct.
Step 12: You can define how the empty field should be handled and choose whether to provide the Help Text and Description.
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: Decide which Page Layout, will receive the new formula field.
Step 17: To save the formula field, click Save.
We can use the formula field once we’ve configured it. Let’s look at an illustration:
- Open the Leads Tab, enter the Lead Transfer Date and the Lead Start Date, and then save the lead.
- The Total Days field, which displays the difference between the two Dates, will then appear in the detail section.
As a result, we now know how to use Salesforce Classic’s advance formula to determine the Formula to Calculate Days Between Dates.
Also, read:
- Salesforce Formula DATETIMEVALUE
- Salesforce Weekday Formula
- Salesforce Formula Field to Check Null Values for Dates
- Salesforce Due Date Formula
Conclusion
We now know how to calculate the number of days between dates using the advanced Salesforce formula.
Furthermore, we discovered that the formula to get days between dates using both Salesforce Classic and Salesforce Lightning.
Plus, the following are the topics that we have discussed:
- Formula for Number of Days Between Dates
- Formula to calculate Days between two Dates in Salesforce Lightning
- Formula to calculate Days between two Dates in Salesforce Classic
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.