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.
Contents
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.
Then, go to 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.
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.
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.
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
