Skip to Content

How to Get Day of The Week from a Date in Google Sheets (Weekday Name)

Posted on Last updated:

Google Sheets comes with various functions for organizing and analyzing dates. You can either use the WEEKDAY or TEXT functions to get the Day of the Week for a given date. The WEEKDAY function gives you a number representing the day of the week for the given date.

On the other hand, the TEXT function is the quickest method to get the day of the week.

You may have seen and managed dates in a spreadsheet often, but it becomes essential to know the day of the week in a few cases.

If you are curious to know the steps to obtain the day of the week from a given date, then you have come to the right place.

Let us understand the Google Sheets functions for getting the day of the week. It is going to be a quick guide with lots of useful information, so make sure to read this article till the end.

We are about to discuss the following methods to determine the day of the week: Click on the links to jump to that particular section of the article.

But before we proceed further, check out the following section.

Download the Example Google Sheet

Consider downloading the following Google Sheet with all the data we will use to demonstrate the steps to get the day of the week in this article.

Click Here To Copy!

It will surely strengthen your skills and knowledge. Follow me along as we move through this article.

Also, it is fine if you have your own dataset ready to practice the concepts and formulas we will discuss in the following section.

How to Get Day of the Week From a Date in Google Sheets using the TEXT function

It is the quickest method to get the day of the week in both Google Sheets and Microsoft Excel.

The TEXT is a relatively simple function in Google Sheets. Here is the General Syntax,

=TEXT(number, format)

While you convert a date to the day of the week, each argument in the above formula must be replaced with proper information, as below.

  • “number” – Here, you need to put the date you wish to convert to the day of the week
  • “format” – You have two options here: “ddd” and “dddd”. When you use the “ddd” format, the day of the week is represented by a shortened 3-letter abbreviation. On the other hand, “dddd” will represent the day of the week by full day.

Task: Get the Day of the Week for the following birthdates of the students. We have a total of 20 records to convert.

So, let us see the steps to get the day of the week using the TEXT function,

  • Type “=text”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Replace the “number” argument with the cell reference “A2”
  • Press “,” on your keyboard to move to the next argument
  • Next, for format argument, we will use “dddd”
    (You need to make sure to use the double quotation marks “”, otherwise you will get an error)
  • Close the bracket using “)”
  • Press “Enter” on your keyboard

The final formula should look like this:

=TEXT(B2,"DDDD")
How to Get Day of the Week From a Date in Google Sheets using the TEXT function

You will notice that Google Sheets instantly converts the given date into the day of the week.

To apply this formula for the rest of the dates, hover to the lower-rightmost corner of the current cell where we have used the TEXT function. You will see a “+” icon. Click on that icon and drag the formula for the rest of the cells, as shown in the above GIF.

As mentioned earlier, using the TEXT function to get the day of the week is pretty straightforward, and you don’t have to rely on any other function, as in the case of the WEEKDAY.

How to Get Day of the Week From a Date in Google Sheets using the WEEKDAY function

The WEEKDAY function in Google Sheets converts a date into a number representing the day of the week. Further, to get the name of the day of the week, you can use the SWITCH function.

Both of these functions are easy to use without any expert knowledge.

So, before we discuss the steps to get the day of the week, let us first understand each function in detail.

Syntax for WEEKDAY function

The WEEKDAY is a simple formula in Google Sheets. The general syntax for the WEEKDAY function is as follows,

=WEEKDAY(date, [type])

Here is the explanation for each argument of the WEEKDAY function,

  • “date” – You need to manually put the date here or provide the cell reference containing the date that needs to be converted to the day of the week
  • “[type]” – This argument accepts various options and lets you decide the key value that represents the starting point for the day of the week
    • “1” is the default value for this argument. It assigns 1 to Sunday and 7 to Saturday. In other words, the week starts on Sunday and ends on Saturday
    • You can also put the number “2” for starting the week on Monday. It gives 1 to Monday and 7 to Sunday
    • The third option is “3”, which starts the week on Monday but assigns a “0” number to the first day of the week (which is Monday) and a “6” number to Sunday

Syntax for SWITCH function

The SWITCH function is an array formula in Google Sheets. The general syntax is as follows,

=SWITCH(expression, case, value)

Here is the explanation for each argument of the SWITCH function,

  • “expression” – The SWITCH function tests each case against an expression for further calculations. Here, you can either manually type the expression or choose the cell reference holding the expression
  • “case” – The value from the expression is compared with the value from this argument
  • “value” – Here, you need to put the output you expect from the SWITCH function. The function is going to compare the expression value with the case value and then provide the outcome based on the “value” argument if everything is matched correctly

Note that the SWITCH function is capable of performing multiple calculations based on the number of “case” and “value” arguments provided.

Now, let us get the day of the week using both of these functions. For the sake of this article, we will divide this process into two steps, as discussed below.

STEP #1 – Get the number for each day of the week using the WEEKDAY function

Let’s consider the usual schedule by assuming that our week starts on Monday and ends on Sunday.

  • Type “=weekday”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Now, to convert the first date, we’ll provide the cell reference as “A2”
  • Press “,” on your keyboard to move to the next argument
  • As we plan to start our week on Monday, we will put “2” in the place of the “[type]” argument
  • Complete the bracket using “)”
  • Press “Enter” on your keyboard

The final formula should look like this:

=WEEKDAY(B2,2)

The first date will be converted into the number that represents the corresponding day of the week. Note that our week starts on Monday, which means 1 will be assigned to Monday, 2 to Tuesday, 3 to Wednesday, and so on till 7 to Sunday.

STEP #2 – Convert numbers to the Days of the Week using the SWITCH function

As we don’t want the output in number formats, we will use the SWITCH function to convert the numbers to the respective day of the week name.

It is an array formula that can perform multiple calculations. In contrast to the IF function, it is compact and easy to use.

We will perform the following conversion using the SWITCH function,

  • 1 to Monday
  • 2 to Tuesday
  • 3 to Wednesday
  • 4 to Thursday
  • 5 to Friday
  • 6 to Saturday
  • 7 to Sunday

For expression, we will use the WEEKDAY function from step 1.

  • Type “=switch”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Next, select the cell reference value as “C2”
    (It is because we need to replace the “expression” argument with a cell reference holding the WEEKDAY function for the corresponding date. In our case, as discussed in the first step, for our first date in “B2”, we are using the WEEKDAY formula in “C2” to get the number for the day of the week)
  • Press “,” on your keyboard to move to the next argument
  • Put “1” to replace the argument “case1”
  • Press “,”
  • Now, as we plan to convert the number “1” to Monday, we will type “Monday” to replace the argument “value1”
    (Make sure to put the double quotation marks on Monday to avoid the error. Refer to the following image)
  • Press “,”
  • For the second conversion, we need to put “2” in the place of the “case2” argument
  • Press “,”
  • As we plan to convert the number “2” to Tuesday, we will type “Tuesday”, making sure that the quotation marks are typed properly
  • For the next 5 days of the week, repeat the above steps from 5 to 8
  • Complete the bracket using “)”
  • Press “Enter”

The final formula should look like this:

=SWITCH(C2,1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")

To apply this formula for the rest of the cells, you can hover to the lower-rightmost corner of the first cell until you see the “+” icon, where we used the SWITCH function, as shown in the above GIF.

And then, simply drag the formula to the end of the table.

Final Words

Getting the day of the week for each date in Google Sheets doesn’t have to be daunting if you have the proper knowledge of TEXT, WEEKDAY, and SWITCH functions.

The TEXT function is the quickest way to get the day of the week. However, using the WEEKDAY function is also pretty straightforward, but it needs to be combined with the SWITCH function to get the day of the week as a text string.

I hope this article helped you master the steps to obtain the day of the week from a given date in Google Sheets.

Let me know in the comment section below if you are stuck somewhere or facing any difficulties converting dates to days of the week.