SharePoint Date Validation Formula to Prevent Selecting Holidays

We often want to prevent the selection of certain days when users enter tasks or events into SharePoint lists.

I have over 100 Calculated Column
and Validation formulas in my book! 

Block weekends

This formula is quite simple and uses the Excel WEEKDAY formula to test the day of the week. WEEKDAY assigns a number to each day using a "scheme". The scheme I'll use here is "2" as it puts Saturday and Sunday as the highest numbers. (You could also use schemes 3 and 11.)

Here's the list of schemes as seen in Excel:
   

Here are two formulas that when used in a SharePoint column validation will prevent the selection of weekend dates. The first eliminates weekend days while the second could be changed to eliminate any pattern of days.

    =WEEKDAY( [yourDateColumn], 2 ) < 6

    =NOT( OR( WEEKDAY( [yourDateColumn], 2 ) = 6, WEEKDAY( [yourDateColumn], 2 ) = 7 ) )

You could also rewrite the last one using ANDs:

    =AND( WEEKDAY( [yourDateColumn], 2 ) <> 6, WEEKDAY( [yourDateColumn], 2 ) <> 7 )

Block Holidays

The ideal solution for holidays would be to create a SharePoint list of holidays. But, lookup columns cannot be used with Calculated Columns or Validation formulas. You can create a workflow to do the lookup, but this will not prevent the selection of the date and will only change a column value after the item has been saved.

So... let's build a formula with a reasonable list of US holidays, say for two years, for a column named [TaskDate]. The OR returns True if any of the date tests are True. The NOT reverses the result because we want to return False as the validation value if the date is a holiday.

Note: Replace [TaskDate] with FLOOR( [TaskDate], 1) if your date column contains dates and times.

=NOT(

     OR(

        [TaskDate]=DateValue("01/01/2019"),
        [TaskDate]=DateValue("01/21/2019"),

        [TaskDate]=DateValue("02/18/2019"),
        [TaskDate]=DateValue("05/27/2019"),
        [TaskDate]=DateValue("07/04/2019"),
        [TaskDate]=DateValue("09/02/2019"),
        [TaskDate]=DateValue("10/14/2019"),
        [TaskDate]=DateValue("11/11/2019"),
        [TaskDate]=DateValue("11/28/2019"),
        [TaskDate]=DateValue("12/25/2019"),
        [TaskDate]=DateValue("01/01/2020"),
        [TaskDate]=DateValue("01/20/2020"),
        [TaskDate]=DateValue("02/17/2020"),
        [TaskDate]=DateValue("05/25/2020"),
        [TaskDate]=DateValue("07/04/2020"),
        [TaskDate]=DateValue("09/07/2020"),
        [TaskDate]=DateValue("10/12/2020"),
        [TaskDate]=DateValue("11/11/2020"),
        [TaskDate]=DateValue("11/26/2020"),
        [TaskDate]=DateValue("12/25/2020")
       )

    )

When this formula is added as a Validation formula to a Date/Time column we can now block the entry of holiday dates!





Combine the weekend formula and the holiday formula and your users can only enter weekdays that are not holidays!

=NOT(

     OR(

        WEEKDAY( [TaskDate], 2 )=6,

        WEEKDAY( [TaskDate], 2 )=7,

        [TaskDate]=DateValue("01/01/2019"),
        [TaskDate]=DateValue("01/21/2019"),
        [TaskDate]=DateValue("02/18/2019"),
        [TaskDate]=DateValue("05/27/2019"),
        [TaskDate]=DateValue("07/04/2019"),
        [TaskDate]=DateValue("09/02/2019"),
        [TaskDate]=DateValue("10/14/2019"),
        [TaskDate]=DateValue("11/11/2019"),
        [TaskDate]=DateValue("11/28/2019"),
        [TaskDate]=DateValue("12/25/2019"),
        [TaskDate]=DateValue("01/01/2020"),
        [TaskDate]=DateValue("01/20/2020"),
        [TaskDate]=DateValue("02/17/2020"),
        [TaskDate]=DateValue("05/25/2020"),
        [TaskDate]=DateValue("07/04/2020"),
        [TaskDate]=DateValue("09/07/2020"),
        [TaskDate]=DateValue("10/12/2020"),
        [TaskDate]=DateValue("11/11/2020"),
        [TaskDate]=DateValue("11/26/2020"),
        [TaskDate]=DateValue("12/25/2020")
       )

    )

.