Show the Day of the Week as Text – Google Sheets

This tutorial shows you two ways to display your dates as the day of the week in your spreadsheet. The default is to show the month, day, and year together. Therefore, you will have to make a few changes depending on the type of output you want.

Grab a copy of this template to follow along with the examples.

Displaying the Date as Text

If you just wanted to show the day of the week as a number, you would use the WEEKDAY function. That’s nice and easy, but this tutorial is not about that, right? This tutorial is about displaying the day of the week as a word. So we’re not going to use the WEEKDAY function and then use that number to look up the day of the week. That’s a little bit too clunky.

Technique # 1 – Apply Custom Formatting

The first way to show the date as the day of the week is to change the formatting. This method doesn’t pull the result of the day of the week into any other cell or formula. This technique changes how the spreadsheet displays the data within a cell.

Select the cell or range you want to change, go up to the Format menu, and go to Number because Sheets stores valid dates as numbers.

Menu with Number formatting selected
Number Formatting Menu Option

Then, go to Custom date and time.

Menu with Custom date and time selected
Custom Date and Time

Clicking on Custom date and time pulls up a menu giving you the options of how to show the month, the day, and the year.

We don’t want the month or year in this case. As shown above, you will need to delete the Year, the Month, and the two dashes that separate them. After deleting those items, the spreadsheet shows the day format as two numbers, 05 in this case. But we want to show the day as a word. Click on the Day selector, and change it to show the full name of the day.

Dates formatted to show days of the week
Days of the Week

Now your spreadsheet should be showing the names of the days. Next, we will cover how to extract the day of the week from a date value.

Technique # 2 – Extract the Day of the Week

Instead of changing the formatting of an existing number, you can pull the day of the week out of a date value and return it in another cell. To do this, we will use the TEXT function.

TEXT Function showing instructions to extract the day of the week
TEXT Function

This function will take a number, and the day of the week is a number, and format it into text. So we will give this function the cell range where the date is. As shown in cell B1 above, the date value is in A1.

The second input for the TEXT function is the format. Remember to surround the value with quotes like "DDDD". Using a single "D" would return the day as a number with no leading zero. "DD" would output the day number with a leading zero. "DDD" would give the shortened version of the day of the week, such as Tue. "DDDD" would provide us with the Tuesday we’re looking for.

Days of the week being extracted
Days of the Week Extracted

Other Date and Time Parts

We can use this same theory to return other parts of a date like the month, minutes, seconds, et cetera. You’ve just gotten a little taste for working with dates in Google Sheets. It can be an eye-opening experience.

Video Tutorials

YouTube player