Categories
Sharepoint

SharePoint Calculated Column WEEKDAY function

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.

Leave a Reply

Your email address will not be published. Required fields are marked *