If you work with large datasets that contain dates and their corresponding days of the week are important, automating the conversion of these dates into days of the week is crucial. 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 produces the corresponding day of the week as a number. The function has the following syntax:
=WEEKDAY(date, [type])
By default, the WEEKDAY function starts counting from Sunday, assigning the value 1 to Sunday. Therefore, the value for Saturday is 7. This is defined as type=1. Since this is the default type of the function, it is not necessary to specify it.
The WEEKDAY function can also be configured to start counting from Monday instead of Sunday. This means that Monday is set as 1 while Sunday is set as 7. To do this, 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 the WEEKDAY Function with the SWITCH Function
We can use another function to convert the output of the WEEKDAY function into its corresponding name. For this, we will use the SWITCH function. The SWITCH function is ideal for cases where you only get a set of specific values to convert. The advantage of the SWITCH function compared to the IF function is that it is more compact and does not require adding IF functions inside another IF function.
If a function you are using returns one of three possible values, let’s say 1, 2, and 3, and you need to convert them to AAA, BBB, and CCC respectively, you can use the SWITCH function as follows:
=SWITCH(expression, 1, "AAA", 2, "BBB", 3, "CCC")
The expression can 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")
Replace cell with the cell reference containing the date. Since the WEEKDAY function is nested inside the SWITCH function, you do not need to allocate an additional column for the value of the WEEKDAY function. You can check the results below:
Example Sheet
You can check our example sheet below:
Crawlan.com – Day of the Week Sample Sheet
Now you know how to find the day of the week of a given date in Google Sheets. This tutorial has shown you how to use the WEEKDAY and SWITCH functions to automate this task. Feel free to give it a try yourself and apply it to your own datasets.
