+ Start a Discussion
Olver_BassovOlver_Bassov 

Help with Validation Rule Formula on Close Date for Opportunities

I have the following validation rule to forbid users altering the close date (field) of the opportunity before the 4th day of the current month although I got a problem because is not taking the desired effect.

The Formula I'm using is:
 
AND(ISCHANGED( CloseDate ),
IF(AND(TODAY() <= DATEVALUE 
(TEXT (Year (Today())) & '-' & TEXT (Month (Today())) & '-' & '04'),
MONTH (CloseDate) < MONTH (TODAY ()) -1)
,TRUE,IF(AND(TODAY() > DATEVALUE 
(TEXT (Year (Today())) & '-' & TEXT (Month (Today())) & '-' & '04'),
 CloseDate < DATEVALUE (TEXT (Year (Today())) & '-' & TEXT (Month (Today())) & '-' & '01')),TRUE,FALSE)))




Can someone with more knowledge than me give me  some light ? It will be much appreciatted 

Thank you
Balaji BondarBalaji Bondar
Hi Olver,

Try below formula :
DAY(DATE(YEAR( CloseDate ),MONTH( CloseDate )+1,1)-1) - DAY(CloseDate) <=4

Important :
If this is what you were looking for then please mark it as a "SOLUTION" or You can Click on the "Like" Button if this was beneficial for you.
Olver_BassovOlver_Bassov
Hi Balaji. 

I'm not sure if I've been clear enough about the rule. After the 4th of the current month you cannot edit or update the close date of the opportunity with the date prior to 4th of the current month. If the close date > 4th current month then you can edit.

Thank you 
Balaji BondarBalaji Bondar
Hi Olver,

If I understand it correctly, this should work:
AND(DAY(Today())>=4 , 
AND(DAY(CloseDate)<=4,
AND(
  MONTH( Today() ) == MONTH( CloseDate),
  YEAR( Today() ) == YEAR( CloseDate)
)
)
)

Important :
If this is what you were looking for then please mark it as a "SOLUTION" or You can Click on the "Like" Button if this was beneficial fo
Venkat PolisettiVenkat Polisetti
Try this:
 
DAY(TODAY()) > 4 && DAY(CloseDate) < 4

 
Olver_BassovOlver_Bassov
Hi Guys I need to validate in a way that prior months closeDate can't be changed at all to any day of any year (past and future). Although the closeDate > 4th current month and year can be changed to any date from 4th this month and so on even next year dates.

My code below do almost all that except the closeDate that is set > 4th this month is getting an error when setting the date to the following years ahead. 
AND(ISCHANGED( CloseDate ), 
IF(AND(TODAY() <= DATE( YEAR(TODAY()),MONTH(TODAY()), 4 ) 
,CloseDate < DATE( YEAR(TODAY()),MONTH(TODAY()), 1) ) 
,TRUE,IF(AND(TODAY() > DATE( YEAR(TODAY()),MONTH(TODAY()), 4), 
OR (CloseDate < DATE( YEAR(TODAY()),MONTH(TODAY()), 1), YEAR ( CloseDate ) <> YEAR ( TODAY() ))) ,TRUE,FALSE)))

Thank you