I was recently asked to setup a KPI indicator which calculates 3 days from a specified date excluding weekends. This is easily achieved using a calculated column in SharePoint, using the WEEKDAY function.
The WEEKDAY function receives a date and returns a number based on the day of the week the provided date falls on.
The syntax is WEEKDAY([DATE],x) where x specified the return type (1,2 or 3).
If no return type is specified it defaults to the first return type (1):
1: 1 Sunday
2: 1 Monday
3: 0 Monday
Logically I knew that if the date fell on Monday or Tuesday then we could add 3 days without needing to adjust for the weekends. Then if it fell on any other day than Sunday, we would need to add 2 days to the standard 3 days and in the case of a Sunday we would just need to add 1 additional day to the KPI calculations.
Since my logic is working forward from Monday, it seems appropriate to use either return type 2 or 3 and I will use return type 2 for this example.
We will also use an IF statement to control the logic with the following Syntax.
IF(TRUE, Then, else)
According to the WEEKDAY function, my logic would be:
If WEEKDAY returns less than or equal to 2 (Monday or Tuesday), then add 3 days to the specified date (No weekend to account for).
IF(WEEKDAY([Date],2)<=2,[Date] + 3,
Else if WEEKDAY returns less than equal 6 (Wednesday to Saturday), then add 5 days (3 days KPI plus 2 weekend days) to the specified date
IF(WEEKDAY([Date],2)<=6,[Date] + 5,
Else add 4 day to the specified date (3 Days KPI plus 1 weekend day being last remaining day Sunday)
[Date] + 4
Then we close our nested statements
The completed formula as entered SharePoint below:
If you run into a similar challenge, I hope this might assist you to make sense of the WEEKDAY function and how you may put it to practical use.