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 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
2: 1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday
3: 0 Monday
1 Tuesday
2 Wednesday
3 Thursday
4 Friday
5 Saturday
6 Sunday
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(WEEKDAY([Date],2)<=2,[Date]+3,IF(WEEKDAY([Date],6)<=2,[Date]+5,[Date]+4))
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.
