Pages

Wednesday, August 28, 2013

Salesforce: Close Date and relation with Current Quarter

In some organizations, Sales Ops team need to control that Close Date for Closed Won opportunity, user should not able to enter any date or change it after Closed Won, because it will effect to forecast, report, and maybe commission.

Here are few use cases related to Close Date with Closed Won opportunity.

1. When user change Opportunity Stage to Closed Won, Close Date must be Current Quarter or after.
Use validation rule below. Of course, you can add exception to the rule not valid for particular profiles.

ISCHANGED(StageName) &&
TEXT(StageName) = "Closed Won" &&
IF (
( YEAR( CloseDate ) < YEAR( TODAY() )) ||
((YEAR( CloseDate ) = YEAR( TODAY() )) && (( MONTH( TODAY() ) >= 4) && ( MONTH( TODAY() ) <= 6) && ( MONTH( CloseDate ) < 4))) ||
((YEAR( CloseDate ) = YEAR( TODAY() )) && (( MONTH( TODAY() ) >= 7) && ( MONTH( TODAY() ) <= 9) && ( MONTH( CloseDate ) < 7))) ||
((YEAR( CloseDate ) = YEAR( TODAY() )) && (( MONTH( TODAY() ) >= 10) && ( MONTH( TODAY() ) <= 12) && ( MONTH( CloseDate ) < 10)))
, TRUE, FALSE)


2. Once the Opportunity has been update to Closed Won, user is not allowed to change the Stage OR change Close Date to different Quarter with stored Close Date.
Same as above, you can add expection to particular profiles.

(ISCHANGED ( StageName ) &&
ISPICKVAL (PRIORVALUE ( StageName ), "Closed Won"))
||
(ISPICKVAL (StageName, "Closed Won") &&
IF(
AND( MONTH(PRIORVALUE( CloseDate )) <= 3, MONTH( CloseDate )<=3, YEAR(PRIORVALUE( CloseDate )) = YEAR( CloseDate )), FALSE,
IF(
AND( MONTH(PRIORVALUE( CloseDate )) > 3, MONTH(PRIORVALUE( CloseDate )) <= 6, MONTH( CloseDate ) > 3, MONTH( CloseDate) <= 6, YEAR(PRIORVALUE( CloseDate )) = YEAR( CloseDate )), FALSE,
IF(
AND( MONTH(PRIORVALUE( CloseDate )) > 6, MONTH(PRIORVALUE( CloseDate )) <= 9, MONTH( CloseDate ) > 6, MONTH( CloseDate) <= 9, YEAR(PRIORVALUE( CloseDate )) = YEAR( CloseDate )), FALSE,
IF(
AND( MONTH(PRIORVALUE( CloseDate )) > 9, MONTH(PRIORVALUE( CloseDate )) <= 12, MONTH( CloseDate ) > 9, MONTH( CloseDate ) <= 12, YEAR(PRIORVALUE( CloseDate )) = YEAR( CloseDate )), FALSE,
TRUE)
)
)
)
)


3. Similar  with point 2, once the Opportunity has been update to Closed Won, user is not allowed to change the Stage OR change Previous Quarter Close Date OR change Close Date to Previous Quarter.

(ISCHANGED ( StageName ) && ISPICKVAL (PRIORVALUE ( StageName ), "Closed Won"))
||
(
TEXT(StageName) = "Closed Won" &&
IF(
AND(MONTH(Today()) <=3, CloseDate>=DATE(YEAR( today()),1,1), PRIORVALUE(CloseDate)>=DATE(YEAR( today()),1,1)), FALSE,
IF(
AND(MONTH(Today()) >3, MONTH(Today()) <=6, CloseDate>=DATE(YEAR( today()),4,1), PRIORVALUE(CloseDate)>=DATE(YEAR( today()),4,1)), FALSE,
IF(
AND(MONTH(Today()) >6, MONTH(Today()) <=9, CloseDate>=DATE(YEAR( today()),7,1), PRIORVALUE(CloseDate)>=DATE(YEAR( today()),7,1)), FALSE,
IF(
AND(MONTH(Today()) >9, MONTH(Today()) <=12, CloseDate>=DATE(YEAR( today()),10,1), PRIORVALUE(CloseDate)>=DATE(YEAR( today()),10,1)), FALSE,
TRUE))))
)