[SalesForce] Validation rule to restrict record creation/Update for future month and year

I wanted a validation to restrict the record creation/ update for following
Fields that need to be restricted % Test (Data Type Percentage), Month (Picklist) Values Jan, Feb ….. Dec)
Year (Picklist) Values ( 2018, 2019 ……)

Expected Result:
1. The user creating/ updating records for past months should not receive the validation error.
2. The user creating/updating records for current/future month should receive the validation error

I have created a custom field formula filed Modified date which is converting the Lastmodifieddate filed from date time to date field.

OR( 
 MONTH(Modified_date__c) <> MONTH(TODAY()), 
 YEAR(Modified_date__c) <> YEAR(TODAY()) 
)

this is not working.
Need help.

Best Answer

Seems like you have a picklist field that contains a month, another picklist that contains a year and a percentage. You want to stop people from changing the percentage field if the month/year are in the future and allow them to change it if the fields are in the past.

I wouldn't use last modified date as I'm not sure how that will behave in the validation rule, instead use Today():

AND(
 ISCHANGED(PercentageField__c),
 MONTH(TODAY()) > VALUE(TEXT(MonthField__c)),
 YEAR(TODAY()) > VALUE(TEXT(YearField__c))
)

Note, that this would require your month field to be a number instead of a string (1, 2, 3, etc instead of jan, feb, dec).

If you absolutely must have jan, feb, dec, etc. as your values. The formula gets kind of crazy (so crazy that I'm not even gonna try to give an example).

You can also consider a workflow that sets a hidden field so that if user picks January, the workflow will set the hidden field to 1 so you can use it in the formula.

Related Topic