1

We have a time off list in SharePoint and we need to make sure the employees select dates that are at least 3 business days in the future. I currently have the formula below in the Column Validation field....

=IF([Requested Day Off]>TODAY()+2,TRUE,FALSE)

This works great during the week, but come Thursday or Friday, they can request Monday off. I have created a another column named [3 Business Days Out] that has a calculated value formula below......

=IF(WEEKDAY(TODAY())>3,TODAY()+5,IF(WEEKDAY(TODAY())>1,TODAY()+3,TODAY()+4))

This returns the magical date I need. But now when I try to place a formula of =[Requested Day Off]>=[3 Business Days Out] in the list validation part of the site, I am getting an error message of "Cannot complete this action. Please Try Again". This has become very frustrating and not sure where I am failing. Any help would be appreciated. Thanks.

  • I posted this awhile back and assumed it was working. Instead, it was preventing them from entering a time of request on the weekend, but on Friday they could still request Monday Off. If a request is entered on a Wednesday, the earliest would be Monday, on Thursday, we want Tuesday to be the earliest, and Friday we want Wednesday to be the earliest. Please help. – Richard Tims Oct 27 '18 at 17:47
0

Your error is because a column validation formula cannot reference any other column. You could use a list item validation formula instead (List Settings, Validation Settings).

For a column validation formula, try this:

=AND( [Requested Day Off] > TODAY()+2, WEEKDAY([Requested Day Off],2) < 6 )

You don't need the IF's because AND, OR and simple comparisons return True or False by themselves.

  • Can you please explain what is happening with that second statement. I want to understand it. I appreciate it. – Richard Tims Sep 24 '18 at 14:20
  • WEEKDAY is an Excel and SharePoint function that returns a number for the day of the week. When the second parameter is set to "2", it returns 1 for Monday, 2 for Tuesday... 7 for Sunday. So, any day that is less than 6 is a week day. support.office.com/en-gb/article/… – Mike Smith - MCT - MVP Sep 24 '18 at 17:44

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.