As a Salesforce admin, I worked on the case report. Recently, the manager wanted to calculate the open cases by the number of days since a customer’s case was created until today in a Salesforce report so that they could track the progress of customer support cases.
I created a case report to achieve this requirement using the difference between the case creation date and today’s date.
Here, I will explain the date formula in the Salesforce report and then show you how to use it using the above scenario and a step-by-step explanation.
Use Date Formula in Salesforce Report
In Salesforce, we can use date formulas in reports to calculate the difference between two date fields and add or subtract days from a date. In the following steps, we will see how to display the date difference from today in the Salesforce report.
1. Go to the Home page. -> Home tab. -> App Launcher. In the search box, search for the Report, and click on the Reports option.
2. Click the New Report button to create the new report.
3. Now, you need to select the Object to create the report. To select the object, you will see the Category option on the left side of the screen. Click the All option, and select Case Object directly or search for the object.
Then click on the Start Report button.
4. Now add the necessary columns by clicking on the outline and searching for a column you want to add; you can also remove unnecessary columns. Here, we need the Date/Time Opened column that we will use in the formula.
5. After that, click on Outline. -> Then click the dropdown arrow. -> Select Add Row-Level Formula.
6. Then, you will see the formula editor interface. Now, you need to fill in the necessary information.
- Column Name: Enter the label of the column you will create. I have given it “Case Opened Days.” The description is optional.
- Formula Output Type: You need to select the output data type. Here, I have selected the Number.
- Formula: In this editor, you must enter the logic and insert the fields you want to calculate. Then, after validating the formula, click the Apply button.
DATETIMEVALUE(TODAY()) - CREATED_DATEONLYHere, we used the Today() function to fetch the current date. From today’s date, we subtract the case-created date to get the number of days between these two dates.
7. Now, you can see the new formula column has been displayed with a name that we entered in the formula editor. It shows the number of days from the cases’ creation to today’s date that the cases were opened.
In this way, we can use the date formula in the Salesforce report to find the number of days between two date fields.
Add a Specific Number to a Date in a Salesforce Report
For instance, if you’ve created a new case and want to find out when it was created, you can use the Salesforce report to determine the date after 11 days. In the following steps, I explained how to add a specific number to a date in the Salesforce report.
1. Go to the report tab and select the object on which you want to add a specific number to the date field.
2. Click the row-level formula so that we can enter the formula and retrieve the date.
3. Now, here, you need to enter the formula.
- Column Name: Enter the label of the column you will create. I have given it “Date After 11 Days.”
- Formula Output Type: You need to select the output data type. We want to display the date here, so select Date in the output data type.
- Formula: In this editor, you must enter the logic and insert the fields you want to calculate. Then, after validating the formula, click the Apply button.
CREATED_DATEONLY + 11Here, to fetch the date after 11 days, the case was created in Salesforce. For that insert, the case created the date and used the + operator to add the number.
4. You can see the formula column has been created with the calculated date 11 days after the case was created.
In this way, we can use the date formula to find the date after adding a specific number to the existing date in the Salesforce report.
Conclusion
I hope you have an idea about the date formula in the Salesforce report. in that, we have seen what the date formula is in the Salesforce report, and then I explain how to display the date difference from today in the Salesforce report with real-time scenarios and step-by-step explanations. Additionally, I explained another example of using the date formula in the Salesforce report.
You may like to read:
- Date Formula Functions in Salesforce
- Create Salesforce Report Group by Month and Year
- Salesforce Date Formula Business Days
- Create a Campaign Report in Salesforce
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.
