A few days ago, a comment posted to this blog asked how to use a calculated field to insure that dates returned are never on a weekend. After thinking about this question for a few days, I decided to approach the answer in two steps. First, how can I make sure that the user never enters a date that is on the weekend. Second, how can I build a calculated date from the date entered and if the calculated date falls on the weekend, to move that date to the following Monday.
To answer the first question, I created a simple event list in SharePoint that would include an Event Title and an Event Date. I wanted the user to enter values into both fields, but wanted to prevent the user from entering a weekend date for the event date. So I began with a Custom List and renamed the default Title column to: Event Title.
Next I added a second column to the list called Event Date and chose Date and Time as the data type for that column. In addition to the normal properties for a date column (like the fact that I want the date only, the date cannot be blank, and the date can default to the current date), I also needed to perform column validation to insure that the date entered was not on a Saturday or Sunday.
In SharePoint 2013, this feature is at the bottom of the column property page. I must click the box to the left of Column Validation to open this property area if a plus sign appears within the small box. When you do, you see a text box area that allows you to enter a Formula. The problem is that the syntax for the formula is not well defined. Although there is a link on the Column Validation section to learn more about proper syntax for formulas, that reference does not list valid functions. I have found, largely through trial and error that the required syntax for column validation more closely corresponds to the syntax of the early DAX syntax in the original release of PowerPivot than to the function syntax in Excel alone. However, a good place to start is to check for an Excel function that meets you need (http://excelfunctions.net/) and only if one is not found or if it generates a syntax error within SharePoint, check for a function in DAX (http://technet.microsoft.com/en-us/library/ee634396.aspx).
Therefore, I looked for a function that would tell me the day of the week given any date. I found the WEEKDAY function which supports 2 parameters. The first parameter specifies the date to test. In this formula, that would be the current column. To specify the current column, just enter the column name. If the column name contains spaces as mine does or could be interpreted as a command, you must enclose the column name in square brackets. Otherwise the brackets are optional.
The second parameter defines how the weekdays are numbered based on the following table.
|1||Week begins on Sunday (1) and ends on Saturday (7)|
|2||Week begins on Monday(1) and ends on Sunday (7)|
|3||Week begins on Monday(0) and ends on Sunday(6)|
I chose to use the second definition. Thus my formula, as shown in the following figure, can look for days with a value of 1 through 5 and return TRUE while a ‘6’ or ‘7’ representing weekends would return ‘FALSE’.
A column value is considered valid if the formula provided here evaluates to ‘TRUE’. If the formula evaluates to ‘FALSE’, SharePoint displays the User Message beneath the date field and does not save the record.
After saving the properties, I can add events to my list. The SharePoint list accepts events that have an event date that correspond to weekdays.
However, if I attempt to enter an event date for a Saturday or a Sunday, SharePoint displays the User Message from the previous figure when the user clicks the Save button. The new item is not added to the list until I select a valid date which in this list is any day from Monday through Friday of any week.
So validating user input is a matter of developing an acceptable formula that returns either a TRUE or FALSE when evaluated with the current value. That’s great for validating input from a user, but does not answer the second case, that of validating a calculated value. I will address this issue in the next post this weekend.
Until then, C’ya.