How to Find the Day of The Week of a Given Date in Google Sheets

If you are handling large datasets with dates, and their corresponding days of the week are found to be important, then you need to automate conversion of these dates to their days of the week. In this tutorial, you will learn two things: the WEEKDAY function and the SWITCH function.

The WEEKDAY function

The WEEKDAY function analyzes a date and then produces the corresponding day of the week in the form of a number. The function has the following syntax:

=WEEKDAY(date, [type])

By default, the WEEKDAY function starts its count on the Sunday, giving the Sunday a value of 1. Therefore, the value of Saturday is 7. This is set as type=1. As this is the default type of the function, there is no need to specify as such.

The WEEKDAY function can also be set up to start counting on Monday instead of Sunday. This means that Monday is set as 1 while Sunday is set as 7. To do so, set type=2:

=WEEKDAY(date, 2)

You can see the result below:

There is a third type, where Monday is set as 0 while Sunday is set as 6. You can see the result below:

Combining WEEKDAY function with SWITCH function

We can use another function to convert the output of the WEEKDAY function to its corresponding name. For this purpose, we will use the SWITCH function. The SWITCH function is ideal for cases where you only get a set of exact values to convert. The advantage of the SWITCH function over the IF function is that it is more compact and does not require you to add IF functions inside another IF function.

If a function you use outputs one of three possible values, let’s say 1, 2, and 3, and you need to convert them to their corresponding meanings AAA, BBB, and CCC, respectively, you can use the SWITCH function as follows:

=SWITCH(expression,1,”AAA”,2,”BBB”,3,”CCC”)

The expression can either be a formula or a cell reference. We will combine this with the WEEKDAY function. The formula now looks like this:

=switch(WEEKDAY(cell),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")

Change the cell to the cell reference containing the date. Since the WEEKDAY function is nested inside the SWITCH function, you do not need to allot an extra column for the value of the WEEKDAY function. You can check the results below:

Sample sheet

You can check our sample sheet below:

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL