ShowAll Questionssorted byDate Posted
cbro

# 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_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 OpFocus

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@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"

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 :)

sandeep@Salesforce

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

Shannon 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.