+ Start a Discussion
LStraubLStraub 

Date validation rule - date must be current or future month with testing for year

Looking for help with a validation rule that makes sure the Close Date on an opportunity where the stage is any value 1 thru 9 is within the current month or a future month.  I have the following rule with works but, it breaks when we get to January of the next year. 

 

(MONTH(CloseDate) < MONTH(Today())) &&
(
(ISPICKVAL( StageName ,"1" )) ||
(ISPICKVAL( StageName ,"2" )) ||
(ISPICKVAL( StageName ,"3" )) ||
(ISPICKVAL( StageName ,"4" )) ||
(ISPICKVAL( StageName ,"5" )) ||
(ISPICKVAL( StageName ,"6" )) ||
(ISPICKVAL( StageName ,"7" )) ||
(ISPICKVAL( StageName ,"8" )) ||
(ISPICKVAL( StageName ,"9" ))
)

 

 

 I almost need 3 tests. 

1 - if YEAR(CloseDate) < YEAR(TODAY()) then check for StageName 1-9

2 - If YEAR(CloseDate) > YEAR (TODAY()) then skip test, no error 

3 - If YEAR(CloseDate) = YEAR(Today()) then run test on month and StageName. 

 

It seems totally doable but i can not figure it out and its making me crazy.  :)   Thanks for any help. 

--Laura

 

Message Edited by LStraub on 05-19-2009 12:06 PM
Best Answer chosen by Admin (Salesforce Developers) 
LStraubLStraub

Thank you very much.  I finally got the nesting syntax right and this works!!  Woot!!

 

IF(YEAR(CloseDate)=YEAR(TODAY()),
(
MONTH(CloseDate) < MONTH(Today())) &&
(
(ISPICKVAL( StageName ,"1" )) ||
(ISPICKVAL( StageName ,"2" )) ||
(ISPICKVAL( StageName ,"3" )) ||
(ISPICKVAL( StageName ,"4" )) ||
(ISPICKVAL( StageName ,"5" )) ||
(ISPICKVAL( StageName ,"6" )) ||
(ISPICKVAL( StageName ,"7" )) ||
(ISPICKVAL( StageName ,"8" )) ||
(ISPICKVAL( StageName ,"9" ))
)
, IF(YEAR(CloseDate) < YEAR(TODAY()),
(
(ISPICKVAL( StageName ,"1" )) ||
(ISPICKVAL( StageName ,"2" )) ||
(ISPICKVAL( StageName ,"3" )) ||
(ISPICKVAL( StageName ,"4" )) ||
(ISPICKVAL( StageName ,"5" )) ||
(ISPICKVAL( StageName ,"6" )) ||
(ISPICKVAL( StageName ,"7" )) ||
(ISPICKVAL( StageName ,"8" )) ||
(ISPICKVAL( StageName ,"9" ))
),false))

All Answers

paynecpaynec

Could you try a nested IF statement in your formula?

 

For example, =IF(YEAR(CloseDate)=YEAR(TODAY()),Test Month Stage,IF(YEAR(CloseDate)<YEAR(TODAY()),Test Stage Only,Skip Test))

LStraubLStraub

Thank you very much.  I finally got the nesting syntax right and this works!!  Woot!!

 

IF(YEAR(CloseDate)=YEAR(TODAY()),
(
MONTH(CloseDate) < MONTH(Today())) &&
(
(ISPICKVAL( StageName ,"1" )) ||
(ISPICKVAL( StageName ,"2" )) ||
(ISPICKVAL( StageName ,"3" )) ||
(ISPICKVAL( StageName ,"4" )) ||
(ISPICKVAL( StageName ,"5" )) ||
(ISPICKVAL( StageName ,"6" )) ||
(ISPICKVAL( StageName ,"7" )) ||
(ISPICKVAL( StageName ,"8" )) ||
(ISPICKVAL( StageName ,"9" ))
)
, IF(YEAR(CloseDate) < YEAR(TODAY()),
(
(ISPICKVAL( StageName ,"1" )) ||
(ISPICKVAL( StageName ,"2" )) ||
(ISPICKVAL( StageName ,"3" )) ||
(ISPICKVAL( StageName ,"4" )) ||
(ISPICKVAL( StageName ,"5" )) ||
(ISPICKVAL( StageName ,"6" )) ||
(ISPICKVAL( StageName ,"7" )) ||
(ISPICKVAL( StageName ,"8" )) ||
(ISPICKVAL( StageName ,"9" ))
),false))

This was selected as the best answer