Salesforce Formula to Calculate Days Between Dates

I was assigned a task at work that required me to calculate the number of days between two dates in Salesforce. To fulfill this requirement, I created a formula field and used an advanced formula to display the number of days.

In this Salesforce Tutorial, we will learn the formula to calculate the number of days between two dates in Salesforce. Additionally, we will review how to utilize Salesforce Lightning to estimate the number of days between two dates.

Salesforce Formula for Number of Days Between Dates

In Salesforce, there are many scenarios where we need to determine the number of days between two 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.

If we have a Date and Time field and 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 the number of days it takes for a lead to convert into an opportunity. To do this, we will calculate the number of days between the lead creation date and the lead conversion date in Salesforce.

Formula to Calculate Days Between Dates in Salesforce

To calculate the number of days between two dates in Salesforce Lightning, the following are the steps that we use:

1. Click the Setup icon in the top right corner of the page to access Salesforce Setup.
   • Select Object Manager from the nav bar.

2. Look for the object whose field you wish to get the days between dates. In this instance, I chose the Orders object from the list of objects.

3. Select Fields & Relationships. Next, select New.

4. Select “Formula“ as the field type. Click Next to proceed.

5. 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.
   • Select the Formula Return Type as Number and also set the Decimal Places to Zero. After that, select Next.

6. In the Advanced Formula editor, enter the advanced formula to determine the Days between two Dates. The formula is as follows:

Expected_Delivery_Date__c -  EffectiveDate 

• In this example, we aim to determine the number of days between the Expected Delivery Date and the Order Start Date.
• Therefore, we subtract the Effective Date from the Expected Delivery Date fields.

7. 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 optional fields.
   • Then, select Next.

8. Select the appropriate field’s level of security, and then check the box beside ‘Visible’ to make it visible to everyone. Click Next.

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

10. 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 an Expected Delivery Date and Start Order Date, and save it.
    • After that, click on the Details section, and the Delivery Days field will appear, showing the difference between the two Dates.

Conclusion

We now know how to calculate the number of days between dates using the advanced Salesforce formula.

Furthermore, we discovered that there is a formula to get the days between dates using both Salesforce Classic and Salesforce Lightning.

You may like to read:

• Salesforce Formula DATETIMEVALUE
• Salesforce Weekday Formula
• Salesforce Formula Field to Check Null Values for Dates
• Salesforce Due Date Formula

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.