Want to transform your dates into days of the week format (e.g., Monday, Tuesday)? Look no further. I got you.
This tutorial will show you ways to convert dates to days of the week format.
Each method has its advantages, so I suggest going over them all. You might find something that you can use in the future.
Convert the Date to the Day of the Week by Changing the Cell’s Format
This method is perfect if you want to keep the actual date, but only view its corresponding day of the week inside the sheet.
(Note: You can view the actual dates from the formula bar whenever you click on the cell).
1. Select the cells containing the dates.
2. From the Home tab, click the small arrow button inside the Number section (as shown in the image below).
You can also just press CTRL + 1 for the keyboard shortcut. Doing so will open the Format Cells menu.
3. Go to the Number tab. From the list of categories, select Custom.
Inside the Type textbox, enter either of the following custom number formats:
| Type | Output |
| ddd | Abbreviation of the day of the week. (Example: Mon; Tue; Wed) |
| dddd | Full name of the day of the week. (Example: Monday; Tuesday; Wednesday) |
As you enter the Type, you’ll see the sample output on top of it.
If you’re happy with the result, click OK.
4. And that’s it! Your dates should now turn into days of the week.
QUICK TIP:
If you want to view the date still and only have its corresponding day of the week next to it, you could instead change the number formatting to “Long Date“.
And then you can copy the formatting to other cells where the new number formatting is needed.
The resulting output will be: [day of week], [month] [day], [year].
Example: Friday, January 13, 2023.
Since the output is now longer, you may need to adjust the width of the columns to have them fit inside.
Convert the Date to the Day of the Week Using the TEXT() Function
This method will work well if you want to allocate a separate cell for the date and another for the day of the week.
On a cell next to the date (or wherever you want to), enter either of the following formulas:
| FORMULA | SAMPLE OUTPUT |
| =TEXT(A2,”ddd”) | Sun; Mon; Tue |
| =TEXT(A2,”dddd”) | Sunday; Monday; Tuesday |
Remember to change A2 with the cell address where your date is.
And that’s it! You’ll have the day of the week added to your sheet.
If you have more dates to convert, simply copy this formula to the remaining cells.
A quick tip: if you want the days of the week to be all in upper case, add the UPPER() function to the formula.
=UPPER(TEXT(A2,”ddd”))
TEXT() Function Explained
The TEXT() function converts a numeric value into text and transforms them based on the format that you have specified.
=TEXT(value, format_text)
It only accepts two parameters:
- value can be a number, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value. In our example above, we have used a cell reference.
- format_text refers to the format to be applied to the numeric value. The value we enter here should be enclosed in double quotes (“). Note that we have used either “ddd” or “dddd” for this parameter because these two are special characters representing the day of the week.
Convert the Date to the Day of the Week Using the WEEKDAY() Function
This method is the same as the previous one, except that this will result in a number that represents the day of the week.
=WEEKDAY(serial_number, [return_type])
The WEEKDAY() function only accepts two parameters:
- serial_number represents the date; you can set the value as the actual date or a reference to the cell containing the date.
- [return_type] is optional. This parameter lets you configure what the first day of the week should be. It also allows you to specify whether the counting should start with 1 or 0. If you leave it blank, the default start of the week will be Sunday, with the counting starting at 1.
You can use the table below as a reference for the return_type to use.
| Return Type | First Day of the Week | Output |
| 1 | Sunday | 1-7 |
| 2 | Monday | 1-7 |
| 3 | Monday | 0-6 |
| 11 | Monday | 1-7 |
| 12 | Tuesday | 1-7 |
| 13 | Wednesday | 1-7 |
| 14 | Thursday | 1-7 |
| 15 | Friday | 1-7 |
| 16 | Saturday | 1-7 |
| 17 | Sunday | 1-7 |
Once you’ve identified the appropriate return_type, you can proceed with writing the formula on an empty cell.
In my example above, I have used this formula: =WEEKDAY(A2,2)
I used “2” as my return_type because I wanted to set Monday as the start of the week. I also wanted to start the count with 1 (not 0).
Also Read: How to Remove Time from Date in Excel?
Convert the Date to the Day of the Week Using the CHOOSE() Function
This method is perfect if you want to customize the days of the week.
Instead of showing the days of the week, you could, for example, display the name of the team assigned to work on a particular day (e.g., “Alpha Team” every Monday).
You can also use this to translate the days of the week into a different language.
In short, this method lets you decide how to display the days of the week.
CHOOSE() Function Explained
The CHOOSE() function displays a particular value from a list of values based on an index number.
=CHOOSE(index_num, value1, [value2], [value3], …)
The CHOOSE() function mainly accepts two parameters:
- index_num can be a number, a formula, or a cell reference that points to a number. This number should only be between 1 and 254. In our case, we will be using the WEEKDAY() function to get the corresponding number of the day of the week.
- value1, [value2], [value3], … represents the output that will appear based on the index_num. You can only add up to 254 values. Don’t forget to enclose the values in double quotes to signify that they represent a text.
You can use the following examples and modify them based on your preference:
- =CHOOSE(WEEKDAY(A2, 2), “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)
- =CHOOSE(WEEKDAY(A2, 1), “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”, “Sun”)
- =CHOOSE(WEEKDAY(A2), “Domingo”, “Lunes”, “Martes”, “Miercoles”, “Jueves”, “Viernes”, “Sabado”)
Don’t forget to change A2 with the cell address where your date is.
Also, remember to change the [return_type] (the second parameter) of the WEEKDAY() function based on what your start of the week should be.
Convert the Date to the Day of the Week Using the SWITCH() Function
This method is the same as the previous one, except with this one, we’ll use a different Excel function.
The SWITCH() function evaluates an expression against a list of values. It then returns the result corresponding to the first matching value. If there is no match, it returns an optional default value.
=SWITCH(expression, value1, result1, [default_or_value2, result2])
- expression refers to a value, formula, or reference to a cell to be evaluated or compared with the specified criteria.
- value1 refers to the first criterion to be compared with the expression.
- result1 refers to the output to display if value1 matches with the expression.
- [default_or_value2, result2] is optional. If this is the last input in your formula, it serves as the default result if the expression doesn’t match any of the previous criteria specified. Otherwise, this serves as the next criterion to be compared with the expression followed by the result if they match.
On an empty cell, copy the following formula and paste it onto your cell. Modify it as needed.
=SWITCH(WEEKDAY(A2,2), 1, “Monday”, 2, “Tuesday”, 3, “Wednesday”, 4, “Thursday”, 5, “Friday”, 6, “Saturday”, 7, “Sunday”)
Don’t forget to update A2 with the appropriate cell containing your date.
Also, remember to update the second parameter of the WEEKDAY() function to ensure that it starts with the correct day of the week.
Aside from converting dates to days of the week, you can also learn to convert the following to certain time and date formats:
Then from there, sort the data by date if needed.
Conclusion
As you can see, there are plenty of ways to transform your dates into days of the week format. I hope you were able to find the suggested methods useful.