This article will explain the Salesforce formula required to calculate the days between dates in Salesforce, as well as how to achieve it in both Salesforce Lightning and Salesforce Classic.
The process of calculating the number of days between two dates is very simple in Salesforce. It is simply a matter of subtracting one date from the other, in the Advanced Formula Editor (as long as the two dates are the same format). Some examples are provided below.
Table of Contents
Salesforce Formula for Number of Days Between Dates
To calculate the number of days between two dates in Salesforce, we need to find the difference between the two date values in the number format by subtracting one date from the other.
The general formula for calculating the number of days between two dates in Salesforce is:
EndDateField - StartDateFieldYou need to start with the end date, or most recent date, as that will have a higher ‘numerical value’, and give you a positive integer.
The other way to calculate the number of days between two dates is to use the DATEVALUE function. This function converts the Date and Time values to date values. The formula using DATEVALUE is:
DATEVALUE(EndDateField) - DATEVALUE(StartDateField)If you have a Date and Time field and we want to calculate only the days between two Dates, then use the DATEVALUE function.
KEY CONCEPT
It is important to note that both the StartDateField and EndDateField are the same type, either DateTime or Date. Otherwise, the formula will not work.
By using these formulas, we can easily calculate the number of days between two dates in Salesforce. Here are two examples using Salesforce Lightning and Salesforce Classic.
Formula to Calculate Days Between Two Dates in Salesforce Lightning
To calculate the number of days between two dates in Salesforce Lightning, follow these steps:
- Click on the “Setup” icon located in the top right corner of the page to access Salesforce Setup.
- Select “Object Manager” from the left side of the menu.
- Look for the object whose field you wish to get days between dates. In this instance, select the “Opportunity” object from the list of objects.
- Select “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. Specify the field label as “Created To Last Modified”.
- Select the Formula Return Type as “Number” and set the Decimal Places to “Zero”. Then, select “Next”.
- In the Advance Formula editor, enter the formula to determine the Days between two Dates. The formula is as follows:
LastModifiedDate - CreatedDate
In this example, the formula calculates the number of days between the “Created Date” and the “Last Modified Date”. Therefore, we subtract the older date (Created Date) from the newer date (Last Modified).
- To verify that the formula is valid, click “Check Syntax”.
- Specify “how you want to handle the empty field” and include the “Description” and “Help Text” as optional fields.
- Then, select “Next”.
- Choose the appropriate field’s level of security and check the box beside “visible” to make it visible to everyone.
- Click “Next”.
- Select the Page Layout Name for which the formula field is to be displayed.
- Click “Save” to save the formula field.
Once the formula field is set up, it can be used. Here is an example:
- Create an Opportunity Report.
- Add the new formula field to the Opportunity Report to see your new calculation in action. You could also add the field to a page layout if it would be useful to display there.
Common Issues With ‘Days Between Two Dates’ Salesforce Formulas
If you get a syntax error when trying to save your new formula, the most likely cause is that you are trying to use two different types of fields.
There are ‘Date’ fields, and there are ‘Date Time’ fields – and they cannot interact directly within formulas.
To use them, you will need to use a modified ‘DATEVALUE’ expression to extract the date value from a Date & Time field.
DATEVALUE(EndDateField) - DATEVALUE(StartDateField)This will extract the date only, from the Date/Time field for use in your calculations.
Formula to Calculate Days Between Two Dates in Salesforce Classic
To calculate the number of days between two dates in Salesforce Classic, the exact same formula described above can be used, but you obviously used navigate the Classic user interface to create the new field, or add the formula to a row-level formula.
If you want a new formula field in Salesforce Classic, enter Setup, and choose the Object you want to add the field to from the left-hand menu in Customize.
Select Add a custom field to contacts from the available options.
Then choose a new Formula field, as demonstrated in the previous example, and follow the same steps.
Other Uses For This Formula
In addition to its use in creating new fields that appear on reports and page layouts, this formula for the number of days between dates can be used to create a row-level formula in reports.
By subtracting one date from another, the resulting value will be the difference between the two initial values in days.
Salesforce Formula Frequently Asked Questions
How to Calculate the Number of Days Between a Date and Today in Salesforce Formula
To calculate the number of days between a date and today, you can subtract the date from the TODAY() function. The formula syntax is as follows:
TODAY() - Date__cThe result will be a number that represents the number of days between the date and today.
What is the Salesforce Formula to Calculate a Date Range
To calculate a date range in the Salesforce formula, you can add or subtract a certain number of days from a given date. The formula syntax is as follows:
Date__c + Number_of_Days__cMake sure that the date is in the Date format and the number of days is a numeric value.
Adding a Certain Number of Days to a Date in a Salesforce Formula
To add a certain number of days to a date in the Salesforce formula, you can use the following formula syntax:
Date__c + Number_of_Days__cMake sure that the date is in the Date format and the number of days is a numeric value.
How to Calculate the Number of Days Between Two Dates Using Salesforce Flow Formula
To calculate the number of days between two dates using the Salesforce Flow formula, you can use the following formula syntax:
{!End_Date__c} - {!Start_Date__c}Make sure that both dates are in the same format, either Date or Date/Time. The result will be a number that represents the number of days between the two dates.