Verify Date Selected Is atleast 3 Business Days Away

Question

We have a SharePoint site that we use to schedule customer appointments. These appointments need to be scheduled on a weekday and need to be at least three business days away, but no more than 45 days away. For example, if they are making a new appointment on a Friday, we can't have it scheduled on the following Monday or Tuesday because it will not give us enough time to prepare the product. Also, we need to prevent them from scheduling the appointment on Saturdays and Sunday. We currently use a 3 days out formula, but need to have a formula that will not count the weekend in the 3 days formula. Please help.

Here is the current "Column Validation" formula we are using: =IF([Requested Day Off]TODAY()+2,TRUE,FALSE)

I have also tried list validation without success. I am open to either.

Answer 1

You can break this down to four basic rules:

• The date of the request cannot be on a weekend.
• The requested date cannot be on a weekend.
• The requested date must be at least three days in the future. (If the request is on Monday then Thursday is the first OK day.)
• If the request date is Wed or later, then the requested date must be at least five days in the future. (to deal with the weekend days)

Here's the four formulas for the above rules, assuming the columns are named "Start" (date of the request) and "Request" (date of the appointment). (Note: the =IF(condition, true, false) in your example can be shortened to =condition as they both return either true or false.

=WEEKDAY(Start,2)<6
=WEEKDAY(Request,2)<6
=(Request-Start)>2
=NOT(AND(WEEKDAY(Start,2)>2,(Request-Start)<5))

All four of these can be combined as a single Item Validation formula using an AND:

=AND( WEEKDAY(Start,2)<6, 
      WEEKDAY(Request,2)<6, 
      (Request-Start)>2, 
      NOT(AND(WEEKDAY(Start,2)>2,(Request-Start)<5))
     )

If in your use Start is the time of entry, then you can replace "Start" with "Today()" and use this as a Column Validation formula.

=AND( WEEKDAY(Today(),2)<6, 
      WEEKDAY(Request,2)<6, 
      (Request-Today())>2, 
      NOT(AND(WEEKDAY(Today(),2)>2,(Request-Today())<5))
     )

These formulas are pretty easy to create and test if you create them first in Excel. (http://techtrainingnotes.blogspot.com/2010/08/sharepoint-creating-calculated-column.html)

Note: The WEEKDAY(value, 2) function numbers the days as Monday = 1 and Sunday = 7.