0

I am creating validation rule which will fire if date of a custom field is greater that first day of next month.

I am using below formula in validation rule but it is not firing.

Custom field is EDate__c with date datatype

eg: if EDate__c is greater the October 1,2021 then validation should fire.

What is missing?

EDate__c > IF(
  MONTH( EDate__c ) = 12,
  DATE( YEAR( EDate__c), 12, 31 ),
  DATE( YEAR( EDate__c ), MONTH ( EDate__c ) + 1, 1 )
)
1

You're using EDate__c too much in this validation rule.

You're performing some date manipulation on a given date, and then comparing it against that same date. The result will always be true, or always be false (depending on the comparison made), and things that never change no matter what input you give do not make for useful validation rules.

You want to be using two separate dates in this validation. One of them is EDate__c, the other is likely something like TODAY().

You really only need to swap out EDate__c for TODAY() in your final call of the MONTH() function, but for ease of reading, I'd suggest replacing all but the first instance of EDate__c.

EDate__c > IF(
  MONTH(TODAY()) = 12,
  DATE(YEAR(TODAY()), 12, 31 ),
  DATE(YEAR(TODAY()), MONTH (TODAY()) + 1, 1)
)

Given that TODAY() = 2021-09-07, this formula simplifies to EDate__c > 2021-10-01, and the validation rule would complain if EDate__c were to be 2021-10-02 or later.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.