Forum Discussion

FSomers's avatar
FSomers
Copper Contributor
Nov 07, 2022

Restricting Holidays in MS List Date Time field

Hello, 

 

We have a simple task tracker with two due date columns that are both date/time columns [Requestor Due Date] and [Due to OPO]. 


Note 1: The "Due Date" shows as DueDate in the hyperlink when I edit that column, but the official name of that column is Requestor Due Date. Not sure if that impacts my validation formula. 

 

I wanted to set column validation on them so they wouldn't allow for the user to select a holiday accidentally as a due date.  I found this article online, and tried copying their outline swapping in Due Date and Due to OPO:  https://techtrainingnotes.blogspot.com/2019/07/sharepoint-date-validation-formula-to.html 

 

 

=NOT(
     OR(
        FLOOR( [Due to OPO], 1)=DateValue("10/10/2022"),
        FLOOR( [Due to OPO], 1)=DateValue("11/11/2022"),
        FLOOR( [Due to OPO], 1)=DateValue("11/24/2022"),
        FLOOR( [Due to OPO], 1)=DateValue("12/26/2022"),
	FLOOR( [Due to OPO], 1)=DateValue("01/02/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("01/16/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("02/20/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("05/29/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("06/19/2023"),
	FLOOR( [Due to OPO], 1)=DateValue("07/04/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("09/04/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("10/09/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("11/10/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("11/23/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("12/25/2023"),
        FLOOR( [Due to OPO], 1)=DateValue("01/01/2024"),
        FLOOR( [Due to OPO], 1)=DateValue("01/15/2024")
       )
    )

 

and 

 

=NOT(
     OR(
        FLOOR( [Requestor Due Date], 1)=DateValue("10/10/2022"),
        FLOOR( [Requestor Due Date], 1)=DateValue("11/11/2022"),
        FLOOR( [Requestor Due Date], 1)=DateValue("11/24/2022"),
        FLOOR( [Requestor Due Date], 1)=DateValue("12/26/2022"),
	FLOOR( [Requestor Due Date], 1)=DateValue("01/02/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("01/16/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("02/20/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("05/29/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("06/19/2023"),
	FLOOR( [Requestor Due Date], 1)=DateValue("07/04/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("09/04/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("10/09/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("11/10/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("11/23/2023"),
        FLOOR( [Requestor Due Date], 1)=DateValue("12/25/2023")
       )
    )

 

Most of the time, the user is able to create or edit a new record just fine. Every so often, they receive errors that aren't my validation error text, such as: 

 

I have even tried to remove the entire list of dates and simply restrict the next holiday (e.g., 11/11) and was willing to update one by one.  That seemed to be working but then today the user got the above error. 

 

Can anyone assist with how to restrict a date/time field with specific dates? 

No RepliesBe the first to reply

Share