How to Get Day of Week from a Date in Google Sheets

Let’s say you manage a team and have a schedule with various dates in Google Sheets. You want to know which day of the week each date falls on so you can better plan your team’s work.

In Google Sheets, you can use different ways to get the day of the week (Name and Number) from a date. In this tutorial, we will learn all these methods in detail. So, let’s get started…

Use the WEEKDAY Function to Get Day from a Date

The WEEKDAY function tells you what day of the week a date falls. For example, if you have a date like “2024-05-25” in cell A1, you can find the weekday using this function.

To do this, write =WEEKDAY(A1) in the cell B1. The function will return a number from 1 to 7, where 1 is Sunday, 2 is Monday, and so on until 7, Saturday.

As you can see, the WEEKDAY function has only two arguments to define the date you want to get the day and the start day of the week.

• date – You can refer to a cell with a valid date or enter the date within the function directly using double quotation marks.
• [type] – Start day of the week. By default, the start day is Sunday, but you can change it with the number you specify; let’s say you want to start it on Monday using 2.

Here’s an example where you can enter the date into the function directly, and when you enter the date, you need to enclose it with double quotation marks.

In this formula, we again have the same date that we have used in the earlier example, and it has returned 5 in the result.

Use TEXT Function to Get the Day from a Date

Now, let’s say instead of the day number, you want to get the day name from a date, in this case, you can use the TEXT function.

Quick Intro to TEXT Function – TEXT functions format the way a date looks by applying the format you choose. It converts dates to text in the format you specify.

The best part is choosing whether to get the name short or long. It can find the abbreviated day of the week from a date. If you have a date like “2024-05-25” in cell A1, this formula will show a short version of the day, such as “Sat” for Saturday. To use it, type =TEXT(A1, “DDD”) in another cell.

=TEXT(A1,"DDD")

The formula below changes the date in cell A1 to the full name of the day of the week, like “Monday.”

=TEXT(A1,"DDDD")

CHOOSE Function to Get the Day

You can use CHOOSE  with WEEKDAY to get a value from a list based on the day number returned by the WEEKDAY. You give it a list of options and a number to pick which option you want.

=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

WEEKDAY(A1) gives a number (1 for Sunday, 2 for Monday, etc.) based on the date in cell A1. And then, CHOOSE uses that number to pick the corresponding day name from the list.

Create a Custom Function to Get Day Number and Name in a Single Cell

Below is the custom function that you can use to get the number and name of the day from the week in a single cell.

And here’s the code.

/**
 * Custom function to get the day number and name from a date.
 *
 * @param {Date} date The date from which to extract the day number and name.
 * @return {String} The day number and name or an error message if the date is invalid.
 * @customfunction
 */
function getDayNumberAndName(date) {
  if (date instanceof Date && !isNaN(date)) {
    var dayNumber = date.getDay() + 1; // getDay() returns 0 for Sunday, so adding 1
    var dayName = Utilities.formatDate(date, Session.getScriptTimeZone(), "EEE");
    return + dayNumber + "-" + dayName;
  } else {
    return "Invalid date. Please enter a valid date.";
  }
}