ExcelInsider Team
Sometimes, you need to know the day of the week while working with dates in Google Sheets for things like scheduling routines, tracking projects, keeping attendance logs, or creating daily sales reports. A date alone doesn’t tell you if it’s a Monday or a Friday, which can affect your plans. If you want to know the day of the week from a date, you probably want to sort or analyze your data by day. Google Sheets has built-in functions that make it easy to change any standard date into a weekday name.
Steps to get the name of the day of the week based on a date in Google Sheets:
➤ Select the cell (e.g., F2) where you want the weekday name to appear.
➤ Type the following formula:
=ARRAYFORMULA(IF(C2:C=””, “”, TEXT(C2:C11, “dddd”)))
➤ Press Enter to fill in the names of the weekdays automatically.
In this article, we will explore several methods to obtain the names of the days of the week from dates in Google Sheets. These include using formulas, custom formatting, and changing the date format as needed. We’ll also answer common questions from users to help you avoid making mistakes and find the best solution.
Using the TEXT Function to Extract Weekday Name
The TEXT function in Google Sheets is one of the easiest ways to change a date into a readable name for a weekday. The TEXT format function uses patterns that the user sets, like “dddd” for full-day names and “ddd” for shorter ones.
In the following dataset, we have a list of dates in one column, and we want to display the full weekday name like “Friday” or “Sunday,” in the next column, F.
Steps:
➤ Select the cell where you want the weekday name to appear; here F2.
➤ Type the formula:
=TEXT(C2, “dddd”)
➤ Press Enter to see the full weekday name.
➤ Drag the Fill Handle if you want to apply it to other cells.
Note:
The TEXT function changes a date into a string that is easy to read. “dddd” gives the full name of the day of the week, while “ddd” gives the short name. Check that the date format in the input cell is correct.
Applying Custom Date Formatting to Show Weekdays
We can show the day of the week right in the same cell as the date by using Google Sheets’ number formatting feature from the menu bar. This is very helpful if you don’t want to add an extra column for the names of the days of the week.
Steps:
➤ Select the cell or range of date cells, here C2:C11.
➤ Go to Format > Number
➤ Click –Friday.
➤ You will see the weekdays in this format:
Note:
This method does not make a new cell with only the name of the day. It’s invasive and changes how the original date looks.
Using WEEKDAY with CHOOSE Function
If you want more control over how the weekday is shown, like changing the names of the weekdays (for example, using a different language or shortcodes), you can use WEEKDAY and CHOOSE together to do it manually.
Steps:
➤ Select the cell beside the first workout date. In this case, A14.
➤ Enter the formula:
=CHOOSE(WEEKDAY(C2), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
➤ Press Enter to apply.
➤ Drag down the Fill Handle to fill the formula for all rows.
Note:
By default, WEEKDAY gives you numbers from 1 (Sunday) to 7 (Saturday). If you want the names of the days of the week in a different language or format, this method works.
Inserting ARRAYFORMULA to Extract Weekday Names from Multiple Dates
If you want to get the names of the days of the week from a whole column of dates without having to drag the formula down by hand, the best way to do it is to use ARRAYFORMULA with TEXT. With just one formula, it automates the whole column. This method is the best way to show the name of the day if you don’t want to change the date value in the column.
Steps:
➤ Select the F2 cell of the output column.
➤ Type the following formula:
=ARRAYFORMULA(IF(C2:C=””, “”, TEXT(C2:C11, “dddd”)))
➤ Press Enter to see weekday names fill down automatically.
Note:
This formula jumps over empty cells so that you don’t get any error messages. It performs best on date columns that are clean and don’t have any breaks.
Combining WEEKDAY with SWITCH Function
You can use SWITCH with WEEKDAY if you want a syntax that is easier to read than CHOOSE. This does the same thing but is easier to work with when there are fewer values or when you need to change something.
Steps:
➤ Click the cell next to the first workout date (e.g., A14).
➤ Type the following formula:
=SWITCH(WEEKDAY(C2), 1, “Sunday”, 2, “Monday”, 3, “Tuesday”, 4, “Wednesday”, 5, “Thursday”, 6, “Friday”, 7, “Saturday”)
➤ Press Enter.
➤ Drag down the Fill Handle to fill the formula for all rows.
Note:
The SWITCH function is simpler to read and use than CHOOSE. Moreover, it is great for creating custom names or lists of some weekdays.
Getting Weekday Name by Using Google Sheets App Script
Google Apps Script can also give you the name of the day of the week from a date. This is practical if you have a lot of data to work with or if you need a custom function that you can use again and again. This method is particularly effective for advanced users.
Steps:
➤ Go to Extensions > Apps Script.
➤ Paste the code below:
function GETWEEKDAYNAME(date) {
if (Object.prototype.toString.call(date) === "[object Date]") {
return Utilities.formatDate(date, Session.getScriptTimeZone(), "EEEE");
}
return "Invalid date";
}➤ Click Save project to Drive.
➤ Click Run.
➤ Select the blank cell F2 of the output column.
➤ Use the function
=GETWEEKDAYNAME(C2)
➤ Press Enter.
➤ Drag down the Fill Handle to fill the formula for all rows.
Note:
The procedure operates similarly to a built-in function. For short names, you can change the format string “EEEE” to “EEE.” It’s effective when you need to share Sheets across time zones or want to have a lot of control.
Frequently Asked Questions
What is the difference between TEXT and WEEKDAY functions in Google Sheets?
The TEXT function changes a date into a formatted string, like “Monday,” and the WEEKDAY function gives you a number from 1 to 7 that shows what day of the week it is. If you want the names of the weekdays, use TEXT. If you want to do math with numbers, use WEEKDAY.
How do I show both dates and weekdays in the same cell?
To show both the day and the full date in one cell, use
=TEXT(A2, “dddd, mmmm dd, yyyy”).
For example, “Monday, January 01, 2025.”
Can I change the first day of the week in Google Sheets?
Yes, the WEEKDAY function has an optional second parameter that lets you choose which day of the week to start. For instance, WEEKDAY(A2, 2) makes Monday the first day of the week.
How to extract just the weekday number from a date?
To find the day of the week, use the formula
=WEEKDAY(A2).
By default, this gives you a number between 1 (Sunday) and 7 (Saturday).
How can I sort data by weekday names in Google Sheets?
Weekday names are text values by default, so they don’t sort by time. You can use WEEKDAY to make a helper column with numbers, and then you can sort by that column.
Concluding Words
If you know how to get or show the names of weekdays from dates in Google Sheets, your datasets will be easier to read and more useful, especially for reports, schedules, and summaries. You have every resource you need to make an appropriate choice now that you know that each method works best for a different situation.
