When you’re tracking data in Microsoft Excel, you can take advantage of the built-in date formatting. However, you may want to display the days of the week instead of the exact dates themselves.
If you prefer to see Sunday instead of 1/1/23 or Saturday instead of 23-Dec-30, we’ll show you a few ways to convert the date to the day of the week in Excel.
Change the Cell Formatting
The easiest way to display weekday names instead of dates in Excel is by changing the cell formatting. When data in a cell is formatted as a date, you can simply change that to a custom format for the day name. This allows you to keep your days in their current location.
- Select the cell, column, or cell range containing the dates you want to change by dragging your cursor through them.
- Do one of the following:
- Right-click and choose Format Cells.
- Go to the Home tab, open the Number Format drop-down box, and choose More Number Formats.
- When the Format Cells dialog box appears, make sure you’re on the Number tab and choose Custom on the left.
- On the right, enter “dddd” in the Type field to display the full name of the day. If you want to use the weekday’s three-letter abbreviation, enter “ddd” instead.
- You’ll see a sample of this format at the top of the box. Select OK to save the change.
You should then have your dates displayed as days of the week.
Tip: Check out our how-to if you’d like to sort by date values in Excel too.
Use the TEXT Function
If you’re familiar with using Excel formulas and don’t mind adding a new column, you can use the TEXT function to display days instead of dates. This is handy if you plan to keep both the weekday and the date or just delete the original dates.
- Go to the first cell in the new column. You can use the column right next to the dates or another in the sheet per your preference.
- Enter the following formula replacing the cell reference B2 with the cell containing the first date in your list. Then, use “dddd” for the full day name or “ddd” for the three-letter abbreviation, each must be within quotation marks:
=TEXT(B2,”dddd”)
- You can then use autofill to copy the formula down to the remaining cells. Select the cell with the formula and drag the fill handle in the lower right corner of the cell downward.
You’ll then have the days of the week for each of your dates in a new column.
Combine the WEEKDAY and CHOOSE Functions
One more way to convert a date to the day of the week in Excel is with the WEEKDAY and CHOOSE functions. While a lengthier process than changing the cell formatting and longer formula than with the TEXT function, it’s still another option. This method also requires a new column for the results.
The WEEKDAY Function
The Excel WEEKDAY function obtains the day of the week from a date but displays it as a number. By default, 1 is for Sunday, 2 is for Monday, and so on. You would use the following formula to get the day of the week for the date in cell B2.
=WEEKDAY(B2)
As you can see, the weekday for our date is a 2, which is Monday.
The CHOOSE Function
The Excel CHOOSE function picks a value from a list of options with the first argument as the value you want to select and the remaining arguments as the list of items. To obtain the first day in our list of weekdays, you would use this formula:
=CHOOSE(1,”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
The WEEKDAY and CHOOSE Functions
Now, you can combine these functions and their formulas to display the weekday names for your dates. You simply insert the WEEKDAY formula as the first argument for the CHOOSE formula which picks the day of the week based on the date in that cell.
Using our above example, we replace 1 with (WEEKDAY(B2)) like so:
=CHOOSE((WEEKDAY(B2)),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
Like with the TEXT function above, you can then use autofill to drag the formula down to the remaining cells to get the rest of the day names for your dates.
What’s the Day for That Date?
You can always pop out a calendar to see which day of the week a certain date falls on. However, if you have a list of dates in Excel, you have a few different ways to display the days instead.
For related tutorials, look at how to subtract dates in Microsoft Excel.
