+ 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
Rajat Singh 46Rajat Singh 46
ilu sharmailu sharma
such great content thanks for sharing with us. keep it up.
 
Very good information posted :  techjustify  (http://techjustify.com) ( http://techjustify.com (http://techjustify.com/) )is a blogging and technology website about science, mobiles, gaming, entertainment gadgets. from analysis of the brand new laptops, games, mobiles, shows, and movies to the latest news about privacy, tech, VPN, environmental policy, and labor.
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