function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cbrocbro 

Date can only be the last day of the month (Validation Rule)

NOT(DAY(EndDate) =28 || DAY(EndDate) =29 || DAY(EndDate) =30 || DAY(EndDate) =31)

 

This covers all possible last day of the month days - but if someone enters June 28th, it will still allow for it to be entered.

 

How can I change this to only allow for the last day of the month to be entered as a date?

 

i.e. 

Jan 31

Feb 28 or 29

Mar 31

Apr 30

May 31

Jun 30

Jul 31

Aug 31

Sep 30

Oct 31

Nov 30

Dec 31

 

Rahul_sgRahul_sg

add more conditions e.g. if month is FEB then only allow 28/29.
If Apr, Jun , Sep or Nov then allow 30 and so on

 

https://help.salesforce.com/HTViewSolution?id=000079759&language=en_US
Boom B OpFocusBoom B OpFocus

Please try this formula:  (replace EndDate with your date field)

 

IF( 
(( MONTH( EndDate) = 1 || MONTH( EndDate) =3 || MONTH( EndDate) = 5 || MONTH( EndDate) = 7 || MONTH( EndDate) = 8 || MONTH( EndDate) = 10 || MONTH( EndDate) =12) && DAY(EndDate) != 31 ), TRUE, 
IF( 
(( MONTH( EndDate) = 4 || MONTH( EndDate) =6 || MONTH( EndDate) = 9 || MONTH( EndDate)  = 11) && DAY(EndDate)  != 30 ), TRUE, 
IF( 
(((MONTH(EndDate) = 2 && MOD(YEAR(EndDate),4) = 0) &&DAY(EndDate)  != 29) || 
((MONTH(EndDate) = 2 && MOD(YEAR(EndDate),4) != 0) &&DAY(EndDate) != 28) ), 
TRUE, 
FALSE 

) 
) 
)
sandeep@Salesforcesandeep@Salesforce

IF(OR(DAY(EndDate) =28, DAY(EndDate) =29, DAY(EndDate) =30, DAY(EndDate) =31),true, false)

 

Please user Dae field API in place of "EndDate"
AdrianCCAdrianCC

Hi,

 

To always get the correct end day of a month you need to first get the 1st day of the next month and from that Date value substract 1 day. 

 

 

DAY(DATE(YEAR(EndDate), MONTH(EndDate)+1, 1)-1)

 

Happy Monday :)

Adrian
sandeep@Salesforcesandeep@Salesforce

Did you try approach I suggested. I think it will meet your criteria.
Shannon HaleShannon Hale

 

Try this for your validation rule formula:

 

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

 

Similar to AdrianCC's suggestion, it gets the last day of the month by subtracting one day from the first day of the next month -- which means it works for leap years without a lot of extra fuss. But AdrianCC's will throw an error for dates in December, because DATE( someyear, 13, 1) is an invalid date -- this one handles that case.
AdrianCCAdrianCC

Thanks! You've found me a bug in an scheduled apex class that I had :)
Shannon HaleShannon Hale
You're welcome!