0

So basically I am trying to setup a sharepoint time off calendar that will only allow users select a range of time off during the same work week - Monday to Friday only. I have edited the calendar to remove Saturday and Sunday so if a user selects an event lasting more than a week in the date range, the calendar does not display correctly. That is to say since Saturday and Sunday have events the calendar gets broken. Please let me know how I can use formulas to make sure my users can only select a range during a single work week. Or if there is an efficient way to remove weekends from month view that still allows users to select more than a week without breaking the calendar, please let me know. Hopefully I have not confused you :)

0

Add a list item validation formula. (List Settings, Validation)

This makes several tests:

  • Dates must be in the right order.
  • Start Date day of week (Tuesday) must be less than End Date day of week (Thursday)
  • The dates must be less than six days apart.
  • Both dates must not be on weekends.

    =AND( [End Date] > [Start Date],
      WEEKDAY( [Start Date], 2 ) < WEEKDAY( [End Date], 2 ),
      [End Date] - [Start Date] < 6,
      NOT( OR( WEEKDAY( [Start Date], 2 )=6, WEEKDAY([Start Date],2)=7 ) ),
      NOT( OR( WEEKDAY( [End Date], 2 )=6, WEEKDAY([End Date],2)=7 ) )
    )
    
0

Adding to Mike's answer, I would recommend including a validation User Message to the effect of: "Start must be prior to End, within the same work week, and both must be working days." Without the validation message, the user only gets List Item Validation Failed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.