In the last post I looked at how to validate a user input column value. In this post, I look at how to manipulate a calculated column to skip the weekends. This validation for a calculated column requires a different technique than last time. A calculated column does not have a column validation option. Rather, I must test for and adapt to different data conditions directly inside the formula for the calculated column using functions to test for valid values. As an example, suppose I created a SharePoint list to track CDs or DVDs from a borrowing library. This is a case in which I could ask the user to enter the date they borrowed the CD (or I can simply use a default value of [TODAY]. Then if I have a lending policy that lets the user borrow the CD for 5 days, I can let the list calculate the due date using a calculated column to add the lending period to the borrowed date. If my borrowing library is open 7 days a week and the standard loan period is 7 days, I could use a calculated formula as simple as:
= [Borrow Date] + 7
This formula calculates the due date by adding 7 days to the borrowed date. But what if my library was not open on weekends. Further, what if the lending period was only 5 days. Therefore, if you borrow a CD on a Friday, it would be due the following Wednesday. On the other hand, if you borrow a CD on a Monday, it would in theory be due back on Saturday, a day on which the library is closed. I might either make the due date the Friday before the Saturday or Sunday, or I could be generous and make the due date the Monday after the Saturday or Sunday.
In this case, I need a slightly more complex formula to calculate the due date to account for weekends. I’ve solved this problem by using a combination of the WEEKDAY() function which I used in the last post in the column validation example and the IF() function. I need to test whether the calculated due date falls on a Saturday and if so, add two more days to the lending period to make the CD due on Monday. On the other hand, if the due date falls on a Sunday, I only need to add a single additional day. I can achieve both results by using a nested IF() function that tests for both conditions as shown in the figure below. Note that if both tests fail, the due date remains just 5 days away.
After saving the formula for the calculated column, I can now test different borrow days. I can still include the formula from last time to make sure that the user does not borrow a book on a Saturday or Sunday as shown below.
However, if I enter a valid date such as April 4th, 2014, SharePoint calculates the due date as April 9th, just 5 days away as I would expect.
If on the other hand I borrow a CD on a Monday like April 7th, 2014, it would be due 5 days later on Saturday, April 12th. Since this is a Saturday, the formula in the calculated field adds two more days to the actual due date to make the cd due on April 14th.
In fact, you can see in the following image that different borrow dates would calculate the due date as 5 days out if that date is a weekday, but would extend the loan period one or two additional days if the initial due date calculates to a Sunday or Saturday respectively to make the new due date a Monday.
I hope these two posts give you a better idea of how to use both the column validation feature for values directly entered into columns by the user and the calculated formula feature for calculated columns to manage calculated results.
C’ya next time.