Validation Rule: No Holidays

Do you have a date field that should never allow holidays? Here’s one way to declaratively (through clicks only) alert the user that the date selected is a designated company holiday. (Thank you, James Garfield for helping me solve this!)

In this example, if a holiday is selected for the custom Delivery Date field, the error “This is a designated holiday. Select another date.” will display.

OR 
(Delivery_Date__c = Date(YEAR(TODAY()),1,1), 
Delivery_Date__c = Date(YEAR(TODAY()),4,18), 
Delivery_Date__c = Date(YEAR(TODAY()),5,26), 
Delivery_Date__c  = Date(YEAR(TODAY()),7,4), 
Delivery_Date__c  = Date(YEAR(TODAY()),9,1), 
Delivery_Date__c = Date(YEAR(TODAY()),11,27), 
Delivery_Date__c  = Date(YEAR(TODAY()),12,25))

Explanation of Dates
1,1 is January 1 (date never changes year after year)
4,18 is Good Friday (date changes from year-to-year)
5,26 is Memorial Day (date changes from year-to-year)
7,4 is July 4 (date never changes year after year)

One caveat, for the holidays where the actual date changes from year-to-year, you’ll need to modify the formula each January. Simply put a reminder on your calendar along with all the other things that require “maintenance” the beginning of each year. Tip: Copy/paste the URL in your calendar reminder for quick access. Also, include instructions in the Validation Rule description.

If you want to apply the same logic to a Date/Time field, here’s the formula that evaluates the date only and ignores the time value (thanks for the help Adam Marks!):

OR
(
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),1,1),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),4,18),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),5,26),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),7,4),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),9,1),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),11,27),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),12,25))

Alternatively, you can reduce the character size of the formula and use this (thank you Deepak Anand for turning me on to this!):

CASE(
    TEXT(MONTH(DATEVALUE(Specific_Date_Time__c))) + ':' +
    TEXT(DAY(DATEVALUE(Specific_Date_Time__c))),
    '1:1',   1,
    '4:18',  1,
    '5:26',  1,
    '7:4',   1,
    '9:1',   1,
    '11:27', 1,
    '12:25', 1,
    0
) = 1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s