Have you ever needed to display the day of the week in your Google Sheets spreadsheet? In this tutorial, I will show you two ways to do it. By default, Google Sheets displays the complete date with the month, day, and year. However, you can make a few changes depending on the desired display format.
Displaying the date as text
If you simply want to display the day of the week as a number, you can use the WEEKDAY function. But this tutorial doesn’t cover that method. Instead, we will focus on displaying the day of the week as a word. We won’t just use the WEEKDAY function to get a number and then look up the corresponding day. That would be too complex.
Technique #1 – Apply a custom format
The first method is to change the display format of the cell. This method doesn’t require the use of formulas or additional cells. It simply changes the way the date is displayed in the cell.
Select the cell or range of cells you want to modify, go to the Format menu, and choose the Number option. Valid dates are stored as numbers in Google Sheets.
Next, select the Custom number format. A window will open, offering you different options to display the month, day, and year.
In our case, we only want to display the day. To do this, remove the year, month, and the hyphens that separate them. After this removal, the cell will display the day format as two digits, for example, 05. But we want to display the day as a word. Click on the day selector and choose the option that displays the full day.
Now, your spreadsheet displays the names of the days.
Technique #2 – Extracting the day of the week
Instead of changing the display format of an existing number, you can extract the day of the week from a date value and display it in another cell. To do this, we will use the TEXT function.
This function takes a number, which corresponds to the day of the week, and formats it as text. So we need to provide it with the range of cells where the date is located. In our example, the date value is in A1.
The second parameter of the TEXT function is the format. Remember to enclose the value in quotation marks, for example, “DDDD”. Using a single “D”, you will get the day as a number without leading zero. “DD” will display the day number with a leading zero. “DDD” will give the abbreviated version of the day of the week, like Tue. “DDDD” will give us the desired Tuesday.
Other date and time elements
You can use the same logic to display other elements of a date, such as the month, minutes, seconds, etc. You have just had a glimpse of working with dates in Google Sheets. It can be a rewarding experience.
Video Tutorials
If you want to learn more about using Google Sheets and discover other tips and tricks, feel free to check out the video tutorials on Crawlan.com. You will find valuable advice to fully exploit Google Sheets’ features.
