You need to sign in to do that
Don't have an account?
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
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
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))
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))