Loading

Common Salesforce Report Formula Examples: Date Difference, ID Extraction, Group Totals, and More

Publish Date: Jun 1, 2026
Description

This article provides ready-to-use report formula examples for the most common calculation needs in Salesforce reports. Use these formulas directly in the Report Builder under Add Column → Formula.

Applies to: Summary reports, matrix reports, and tabular reports in Lightning Experience and Salesforce Classic.

Report Formulas Require Specific Function Syntax

Salesforce report formulas use a specific set of functions. Unlike spreadsheet formulas, report formulas reference fields by API name and have two types: summary formulas (calculated at a group level) and row-level formulas (calculated per record). Choosing the wrong type is a common source of errors.

Resolution
Formula 1: Number of Days Between Two Date Fields

Use case: Calculate days to close, days since last activity, or age of a record.

Type: Row-Level Formula | Return Type: Number

  • Formula: CloseDate - CreatedDate
  • This returns the number of calendar days between the two dates as a decimal number.
  • For days-to-convert on leads: ConvertedDate - CreatedDate
Note: Date arithmetic in report formulas produces a decimal. Use the ROUND() function if you want a whole number: ROUND(CloseDate - CreatedDate, 0)
Formula 2: Extract 15-Character Salesforce Record ID

Use case: Display a 15-character ID instead of the 18-character version for use in external systems or comparisons.

Type: Row-Level Formula | Return Type: Text

  • Formula: LEFT(Id, 15)
  • This extracts the first 15 characters of the record's 18-character ID.
Formula 3: Percentage of Group Total (PARENTGROUPVAL)

Use case: Show what percentage each row or group contributes to the grand total. Requires a summary report with at least one grouping.

Type: Summary Formula | Return Type: Percent

  • Formula: RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)
  • Replace RowCount with your summary field (e.g., Amount__c:SUM) to calculate percentage of revenue by group.
Warning: PARENTGROUPVAL is only available in summary formulas, not row-level formulas. It will not appear in the formula builder for tabular reports.
Formula 4: Map Day of Week to a Number for Sorting

Use case: Group or sort records by day of the week in chronological order (Mon=1, Tue=2, etc.) rather than alphabetical order.

Type: Row-Level Formula | Return Type: Number

  • Formula: CASE(MOD(ActivityDate - DATE(1900,1,7), 7), 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6, 7, 0)
  • This maps Sunday=0, Monday=1, Tuesday=2, ..., Saturday=6. Adjust the mapping values as needed.
Formula 5: Win Rate on Closed Opportunities

Use case: Calculate the percentage of closed opportunities that were won. Requires a summary report grouped by Stage or Owner.

Type: Summary Formula | Return Type: Percent

  • Create a summary report on Opportunities grouped by Owner.
  • Add a cross-filter or filter for Stage = "Closed Won" to get a won count, and a separate report for all closed. Alternatively, use a formula: this typically requires two separate reports joined in a dashboard widget rather than a single formula.
See KB article Calculate Win Rate on Closed Opportunities in a Report for a dedicated walkthrough.
FAQ
Q: What is the difference between a row-level formula and a summary formula?
A row-level formula calculates a value for each individual record (row) in the report, like a per-row date difference. A summary formula calculates a value at a grouping level (subtotal or grand total), like a percentage of the total. You select the type when adding a formula column in the Report Builder.
Q: My formula returns a very long decimal. How do I show only two decimal places?
Wrap your formula with ROUND(): ROUND(your_formula, 2) to display two decimal places.
Q: Can I use IF statements in report formulas?
Yes. Use the IF() function: IF(condition, value_if_true, value_if_false). For multiple conditions, use nested IF() or the CASE() function.
Knowledge Article Number

005385906

 
Loading
Salesforce Help | Article