Sometimes you might want to use Google Sheets to determine what day of the week a specific date is.
That can be done with the WEEKDAY function.
In this tutorial, I will show you how to use the WEEKDAY function in Google Sheets.
Table of Contents
How to Use the WEEKDAY Function
The WEEKDAY function returns a number that represents the day of the week when a date is put into the function.
You have a few different options on just how exactly Google Sheets will count the days, but we will cover that in the next section when I go over the syntax of this function.
WEEKDAY Syntax
The syntax of the WEEKDAY function is:
=WEEKDAY(date, [type])
- Date – this argument is the date that will be used to determine the day of the week. It has to be a cell containing a date, a function that returns a date, or a number
- Type – this is an optional argument that represents how Google Sheets will count the days. For this argument, you can set it to 1, 2, or 3. 1 is the default setting if you do not use this argument in your formula
- 1 – this counts days starting from Sunday where Sunday starts as 1, so Saturday is 7
- 2 – this counts days starting from Monday where Monday starts as 1, so Sunday is 7
- 3 – this counts day starting from Monday where Monday starts as 0, so Sunday is 6
Using the WEEKDAY Function
Now that we have gone over the basic syntax, let’s take a look at how to actually use it in your spreadsheet by looking at the example data below:
You can see here how simple the formula is to use. The only argument is the cell containing your date, or you can specify how Google Sheets counts days by putting a 1, 2, or 3 as the optional second argument.
In the data above, look at how the first four dates are the same and how the result changes. The first two formulas are equivalent since leaving out the second argument is the same as using a 1, so the result is the same. But the third and fourth formula, the result changes because each day of the week is counted differently.
Remember that the WEEKDAY function only returns a number that represents the day of the week. If you want to actually return the name of the day of the week, we will show you how to do that below.
How to Use the WEEKDAY Function to Show the Day of Week
If you want to convert a date to the day of the week, and actually display the name of that day, that can be done in Google Sheets.
But you can’t do it with just the WEEKDAY function alone. A great way to do this is by combining it with the CHOOSE function.
The CHOOSE function will return a value from a list of choices based on the index.
The syntax of the CHOOSE function is:
=CHOOSE(index, choice1, [choice2, …])
- Index – this argument is the number of which value in the list to return. If it is 1, it will choose the first value in the list, 2 the second value etc…
- Choice1 – this is the value to return. It can be a cell reference or a value entered into the function itself
- Choice2 – you can enter additional values into your list here. This argument is optional.
Let’s look at an example of how this function is used in Google Sheets.
You can see in the example above, the function just returns the value in your list that is at the index number of your first argument. 1 will choose the 1st option, 2 will choose the second, three will choose the 3rd, and so on.
So now you might be beginning to understand how to combine the CHOOSE function with WEEKDAY to display the day of the week.
Since WEEKDAY returns a number, and CHOOSE will take a number and return a value from a list, you can combine them to show the day.
Here is the exact formula that I will use in my spreadsheet to do this:
=CHOOSE(WEEKDAY(A2),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
You can see in the data above that by combining these two functions, instead of just returning a number that represents the day, I can actually display the name of the day itself.
How to Display the Current Day of the Week
You can take this one step further and combine the two functions we covered above to display the current day of the week by adding the TODAY function as the argument for your WEEKDAY function.
I am not going to go over the specifics of how to use the TODAY function in this post. You can read on that in my guide on how to insert the current date in Google Sheets.
To combine WEEKDAY, CHOOSE, and TODAY to display the current day, we want to set up our formula like this:
=CHOOSE(WEEKDAY(TODAY()),“Sunday”,“Monday”,“Tuesday”,“Wednesday”,“Thursday”,“Friday”,“Saturday”)
Here is an example of it is used in my spreadsheet:
Closing Thoughts
The WEEKDAY function is a very useful function to use while working with dates in Google Sheets. We have covered some of the possible ways to use it in this post, but after you learn it, there are a number of other things you can do with it.
Make sure to go through all the examples I have covered and try to understand how each works so that you can master this.
More Google Sheets Tutorials:
How to Sort by Date
How to Use the ISDATE function
How to Use the EDATE Function
How to Use the WEEKNUM Function