Scenario
User is creating an Opportunity Saved Search and needs a formula that shows count of days between Expected Close Date and Actual Close Date fields. The formula should show:
- the days Past Expected Close Date
- the days Before Expected Close Date
- if the Actual and Expected Close Dates match
Solution
In the Results tab of the Saved Search, use Formula (Numeric) fields with the below formula:
- Days past expected: CASE WHEN {expectedclosedate} < {closedate} THEN {closedate} - {expectedclosedate} END
- Days before expected: CASE WHEN {expectedclosedate} > {closedate} THEN ABS({closedate} - {expectedclosedate}) END
- Dates match: CASE WHEN {expectedclosedate} = {closedate} THEN 0 END
Alternatively, a single formula field can be used to show the details if it is past expected, before expected or dates match. Here's the formula to use in a Formula (Text) field:
- CASE WHEN {expectedclosedate} = {closedate} THEN 'Dates Match' WHEN {expectedclosedate} < {closedate} THEN ({closedate} - {expectedclosedate}) || ' days past expected' WHEN {expectedclosedate} > {closedate} THEN ABS({closedate} - {expectedclosedate}) || ' days before expected' END
Here's a sample of what the search results will look like:
-----
If you find this information useful, let us know by reacting or commenting on this post!