function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nikita ShatalaevNikita Shatalaev 

Validate if Close Date is withing specific range after saving

Hello, 

I need to create a validation rule that checks if the CloseDate of edited Opportunity is within specific date range and prevents saving if not. 
Here is the example of the rule I have and need to be modified: 

AND (
ISCHANGED(CloseDate),  
PRIORVALUE(CloseDate)>DATE(2015,9,30),
PRIORVALUE(CloseDate)<DATE(2016,1,1),
$Profile.Name <> "System Administrator")

So this rule prevents editing Close Dates of opportunities within current quarter if you are not system administrator. What do I need to add here to allow saving if CloseDate remains within current quarter, but still restrict it if it moves past December 31st 2015? 

I tried adding string CloseDate_c > DATE(2015,12,31) but that didnt seem to work. 

Ideas please? 
pconpcon
I'm not sure I understand what you mean by "if CloseDate remains within the current quarter"  are you saying that you want them to be able to modify it but only modify it so that it stays within those date ranges?
  
AND (
ISCHANGED(CloseDate),  
PRIORVALUE(CloseDate)>DATE(2015,9,30),
PRIORVALUE(CloseDate)<DATE(2016,1,1),
CloseDate<DATE(2015,9,30),
CloseDate>DATE(2015,12,31),
$Profile.Name <> "System Administrator")

That will throw an error if the date is change, if it's old value was inside the quarter and the new value is not in the quarter and the profile is not an admin.
Nikita ShatalaevNikita Shatalaev
No, that doesn't seem to work. Since I have validation rule that prevents setting Close Date in the past, there is no real need in string CloseDate<DATE(2015,9,30) in my rule. But it still allows me to do any changes with close dates if I use either variants (both strings, or CloseDate>DATE(2015,12,31) only). I tried removing to Profile Name restriction string to check this and it allows me to do any changes with Close dates. 
Tenacious BenTenacious Ben
I think in pcon's example above, an OR condition was missing when comparing the new CloseDate (lines 5 & 6).  Since you've mentioned that you already have a validation rule in place for preventing a close date in the past, this validation should work: 
AND ( 
  ISCHANGED(CloseDate), 
  PRIORVALUE(CloseDate) > DATE(2015,9,30), 
  PRIORVALUE(CloseDate) < DATE(2016,1,1), 
  CloseDate > DATE(2015,12,31), 
  $Profile.Name <> "System Administrator" 
)
However rather than hardcoding the dates in the validation rule, you could also programatically check that the prior close date is in the current FQ: 
AND (
  ISCHANGED(CloseDate),
  AND(
   CEILING(MONTH(PRIORVALUE(CloseDate))/3) = CEILING(MONTH(TODAY())/3),
   YEAR(PRIORVALUE(CloseDate)) = YEAR(TODAY())
  ),
  OR(
   CEILING(MONTH(CloseDate)/3) > CEILING(MONTH(TODAY())/3),
   YEAR(CloseDate) > YEAR(TODAY())
  ),
  $Profile.Name <> "System Administrator"
)
The validation rule above will check that
1) the close date is changing
2) determine if the prior value of the close date is in the current FQ (more info here: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm)
3) determine if the new close date is later than the current FQ
4) check to see if this is not a system admin

 
Nikita ShatalaevNikita Shatalaev
I will try that, but we have shifted Fiscal Quarters in our organization, so unified rule might not work as intended for us. 
Tenacious BenTenacious Ben
If any of these answers worked for you, please mark one as the best answer.  If not, post a followup and we'll help as best we can.
Rizwan Ali 8Rizwan Ali 8
@Tenacious Ben Please Can you explain this Formula
User-added image