NSC | Formula that Compares Expected Close Date & Actual Close Date in an Opportunity Search

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