SharePoint: WEEKDAY Function

Knowing the day of the week can be incredibly useful when it comes to formatting data. For example, you can have a column representing a value during weekdays and no value during weekends. Or calculate the rate of someone based on if it’s a weekday or weekend. The WEEKDAY function hides a few nice tricks, so let’s explore them.

Usage

It follows a simple pattern.

Serial Number
Return Type (optional) – number from 1 to 3, where 1 is the default.

Internally dates are stored as sequential numbers from a reference day. They follow the same structure as Excel, so if you have questions here’s how to convert a number to date (and vice-versa)

For the first example, let’s not define the “return type” and use the number “44329,” which refers to today’s date (13th May 2021) that is a Thursday.

=WEEKDAY(44329)

will return 

5

By default, SharePoint will define 1 (Sunday) through 7 (Saturday).

So far, so good. But some countries, like my own, define the first day of the week as Monday, not Sunday. Microsoft helps us with that with the second parameter. The second parameter can be a number from 1 to 3 that defines 3 different return types:

This is the default and used if you don’t provide any value and define return 1 (Sunday) through 7 (Saturday).
Will change the return to 1 (Monday) through 7 (Sunday)
Will change the return to 0 (Sunday) through 6 (Saturday)

Confused yet?

The idea is to have the return that fits your needs. Let’s get the same example, but test all return types:

=WEEKDAY(44329)

will return 

5

--- 

=WEEKDAY(44329,1)

will return 

5

--- 

=WEEKDAY(44329,2)

will return 

4

---

=WEEKDAY(44329,3)

will return 

3

Numbers are not user-friendly. Can’t we use dates?

Using Dates

The short answer is yes; we can. There are no long answer :).

=WEEKDAY(Today())

will return 

5

In this case, we’re using today’s date (13th May 2021) to know it’s a Thursday. So when you commit the “return type,” Sunday is 1, and Saturday is 7.

Let’s test with the other return types:

=WEEKDAY(Today())

will return 

5

--- 

=WEEKDAY(Today(),1)

will return 

5

--- 

=WEEKDAY(Today(),2)

will return 

4

---

=WEEKDAY(Today(),2)

will return 

3

Strings

You can also use strings as a parameter, but you need to be cautious using them. Let’s look at the following example:

=WEEKDAY("2021-05-13",3)

will return 

3

This case is clear that we refer to the “13th of May 2021”. But what about?

=WEEKDAY("05-04-2021",3)

will return 

1

If you ask multiple people what’s the date here, people will tell you either the 5th of April or the 4th of May. And both of them will be right in their own location.

I won’t discuss the “correct” format for dates (I want to but let’s leave that aside now), but I would strongly advise not to use strings since this is not deterministic. You may be displaying incorrect data to half of your users.

Limitations

If you get the values from Excel, you may get decimal numbers (to represent the time). Be very careful because, depending on how you build the formula, you may have invalid records. You don’t need the time to know the day of the week, so remove it if you can or deal with it if you can’t get it not to display invalid values to your users.

Recommendations:

Always define the return type

I know that it’s not mandatory, but defining it forces you to think about the format that must be returned. Depending on what you define, 1 can Sunday, Monday, or Tuesday, so there’s a huge difference if you get the number wrong.

Always use the same return type.

Regardless of what you choose, always pick the same one in all your formulas. Like I mentioned before, the number 1 can be Sunday, Monday, or Tuesday, so in all formulas, use the same return type so that you’re always in the same “mindset.”

Ideal for localization

With this function, you can adapt your return to the proper localization. You can have multiple columns that display the data based on the user’s preferences, like mine, where the week starts on Monday, instead of others that think of Sunday as the start of the week. It’s a small difference but super important.

“Hidden” Validation Columns

Since SharePoint List works with columns, you can create hidden “validation” columns with the functions that you can use to validate if the data is correct or not. After that, you can expose to the user the “message” columns that have useful error messages or actions that they can take to solve the problem. In this case, the “message” can be the formatted day of the week.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your own code. If you don’t validate a column, it can be used after other calculations, and the error will cascade. You can have an error in one column, and the issue could be in whole different columns, so deal with issues where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested WEEKDAY functions in a formula, you should review it and make everything more straightforward.

Please note that formulas may have localization-based differences. For example, you should write “WEEKDAY” with a comma separating each of the arguments, but if your regional settings are set to Portugal, you should use “;” instead.