Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Cody PiersonCody Pierson 

Restricting Close Date to Last Day of the Month When Not Closed Won/Lost

Hi there!

I've been asked to restrict the valid Close Dates for Opporunities to the last day of the month when the Stage is not Closed Won/Lost.

I've found a fromula that does a great job restricting the dates in general, but I can't figure out how to add the additional condition that it should only apply the date limitation when the Opportunity is in certain stages.

IF( 
    MONTH( CloseDate ) = 12,
    DATE( YEAR( CloseDate ) + 1, 1, 1 ),
    DATE( YEAR( CloseDate ), MONTH( CloseDate ) + 1, 1 )
  ) - 1 
)

Any help/insight would be greatly appreciated!
Best Answer chosen by Cody Pierson
Maharajan CMaharajan C
Hi Cody,

If you want to exclude for Closed Won / Lost... Then Use the below Validation Rule...
  
AND(
NOT( ISPICKVAL(StageName, "Closed Won") ),
NOT( ISPICKVAL(StageName, "Closed Lost") ),
CloseDate != (
IF(
MONTH( CloseDate ) = 12,
DATE( YEAR( CloseDate ) + 1, 1, 1 ),
DATE( YEAR( CloseDate ), MONTH( CloseDate ) + 1, 1 )
) - 1
)
)

Thanks,
Maharajan,C

All Answers

Maharajan CMaharajan C
Hi Cody,

If you want to exclude for Closed Won / Lost... Then Use the below Validation Rule...
  
AND(
NOT( ISPICKVAL(StageName, "Closed Won") ),
NOT( ISPICKVAL(StageName, "Closed Lost") ),
CloseDate != (
IF(
MONTH( CloseDate ) = 12,
DATE( YEAR( CloseDate ) + 1, 1, 1 ),
DATE( YEAR( CloseDate ), MONTH( CloseDate ) + 1, 1 )
) - 1
)
)

Thanks,
Maharajan,C
This was selected as the best answer
Maharajan CMaharajan C
Also if you want to add the certain Opportunity stages and Stage is not Closed Won/Lost then use the below Validation Rule:
  
AND(
	NOT( ISPICKVAL(StageName, "Closed Won") ),
	NOT( ISPICKVAL(StageName, "Closed Lost") ),
	OR(
	   ISPICKVAL(StageName, "Prospecting"),
	   ISPICKVAL(StageName, "Qualification"),
	   ISPICKVAL(StageName, "Needs Analysis"),
	   ISPICKVAL(StageName, "Value Proposition"),
	   ISPICKVAL(StageName, "Negotiation/Review")
	  ),
	CloseDate != (
	IF(
	MONTH( CloseDate ) = 12,
	DATE( YEAR( CloseDate ) + 1, 1, 1 ),
	DATE( YEAR( CloseDate ), MONTH( CloseDate ) + 1, 1 )
	) - 1
	)
)

Thanks,
Maharajan.C
Cody PiersonCody Pierson
Thanks, Maharajan! The first iteration was exactly what I was looking for, but it's great to have the version with extra flexibility in my back pocket if I need it!
vfdbg gfsgfvfdbg gfsgf
Oh thanks sir fr giving this detailed guidance in the reply I applied it on this project (https://topbestnespresso.com/) where I was also facing this issue and now it resoled.
Ari JobsAri Jobs
Oh, thanks sir fr giving this detailed guidance in the reply I applied it to this project BBC Bangla (https://sangbadworld.com/bbc-bangla/) where I was also facing this issue and now it resoled.
K P 51K P 51
Hey, visit https://www.techwithkp.com/
Rajat Singh 46Rajat Singh 46
slptophp (https://slptophp.net)
Offcampus freshersOffcampus freshers
Thank you for including this information in your post. This is a fantastic post! By the way, I'd like to share some details with you about the best. Off Campus Jobs For Freshers in Bangalore