Slipped Deal Tracking Salesforce setup

Hi ​@Peggy Sue Marlin— Thanks for reaching out and sharing this question! 

From reading the post you linked and looking at what you’re hoping to accomplish, I believe a validation rule similar to the following could do the trick.  In the validation rule, you’d want to replace Slipped_Deal_Reason__c with the API name for the field your team’s “Slipped Deal Reason” field.  (Please note that this validation rule works best if your ‘Slipped Deal Reason” field is a text field given the requirement that reps must re-enter the reason.)

AND(
    ISCHANGED(CloseDate),
    DATE(
        YEAR(CloseDate),
        CEILING(MONTH(CloseDate) / 3),
        1
    ) > DATE(
        YEAR(PRIORVALUE(CloseDate)),
        CEILING(MONTH(PRIORVALUE(CloseDate)) / 3),
        1
    ),
    OR(
        ISBLANK(TEXT(Slipped_Deal_Reason__c)),
        NOT(ISCHANGED(Slipped_Deal_Reason__c))
    )
)

The AND function would ensure that the following three conditions are met before the validation rule fires.

1. ISCHANGED(CloseDate) 
   • This is the first and most critical condition. It checks if the CloseDate field has been modified from its previous value. The validation rule will only run if this is true.
2. DATE(YEAR(CloseDate), CEILING(MONTH(CloseDate) / 3), 1) > DATE(YEAR(PRIORVALUE(CloseDate)), CEILING(MONTH(PRIORVALUE(CloseDate)) / 3), 1)

   • This is the core logic for checking if the quarter has changed.

   • CEILING(MONTH(CloseDate) / 3): This function calculates the quarter number for any given month. For example:

     • January (1) -> 1/3 = 0.33 -> CEILING(0.33) = 1 (Q1)

     • April (4) -> 4/3 = 1.33 -> CEILING(1.33) = 2 (Q2)

   • DATE(YEAR(), CEILING(), 1): This creates a date object for the first day of the quarter for both the new CloseDate and the old PRIORVALUE(CloseDate).

   • By comparing these quarter start dates, the formula accurately determines if the new CloseDate is in a quarter that is later than the previous one.

   • The validation rule will only run if the CloseDate is determined to be updated to a month that falls into a future quarter (whether that is a future quarter in the same year or a future quarter in a future year).  

3. OR(ISBLANK(TEXT(Slipped_Deal_Reason__c)), NOT(ISCHANGED(Slipped_Deal_Reason__c)))

   • This final condition handles the "Slipped Deal Reason" field. The validation rule will fire if:

     • ISBLANK(TEXT(Slipped_Deal_Reason__c)): The Slipped_Deal_Reason__c field is currently empty.

     • NOT(ISCHANGED(Slipped_Deal_Reason__c)): The user did not change the value of the Slipped_Deal_Reason__c field, even if it already had a value.

   • The OR function means that if either of these conditions is true, the entire rule evaluates to TRUE (assuming the first two conditions are also met), preventing the user from saving the record.

You can also include some error message text that will show to the end-user if they update the “Closed Date” without (re-)entering a “Slipped Deal Reason”.  That text could look something like: “You must provide a new value for the "Slipped Deal Reason" field when updating the Close Date to a future quarter.”

A Salesforce admin on your team can help with building a validation rule like this through the following steps: 

1. Navigate to Setup in Salesforce.

2. Go to Object Manager.

3. Select the Opportunity object.

4. Click on Validation Rules in the left-hand menu.

5. Click the New button and enter the formula and error message.

As with any Salesforce customizations, we would recommend testing out the validation rule on some test records under various conditions to make sure it is responding as desired when records are updated.  In case it’s helpful, I also wanted to share some other resources that may be helpful when thinking through how to build out this and other validation rules:

• https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A85yHSAR
• https://www.salesforceben.com/validation-rules-in-salesforce/
• https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A8Jy9SAF

Hope this helps! 

Wow, ​@elijah, thanks for the detailed breakdown! I’m going to share this with our Salesforce admins. I’m also learning Salesforce myself, so your explanation of the formula is super helpful.

Hi ​@Peggy Sue Marlin— Anytime!  Hope that helps give your team a solid starting point for implementing a validation rule like the one you described.  If you or your team have any further questions, I’d be happy to weigh in to the best of my ability.  

Hi ​@elijah , our Salesforce team has built this validation rule and it’s working almost perfectly. But we’ve run into a snag in the scenario where the user has pushed the date and entered a reason once, and is now pushing the date again. We do need the user to  “re-enter” the Slipped Deal Reason, but it could be the same reason as before. The validation rule you provided requires the reason to be different. They’re considering creating a Screen Flow for this in Salesforce, but I think we need it to be a Validation Rule for it to be usable in Clari, right? Any ideas?