You have a column of dates in Excel and need to see which day of the week each one falls on. Adding a separate column for the weekday can be inefficient and clutter your sheet. Excel’s custom number formatting provides a direct solution. This article explains how to use the “dddd” format code to display the full weekday name alongside your dates without altering the underlying cell value.
Key Takeaways: Display Weekdays in Excel
- Custom Format “dddd”: Applies the full weekday name to a date cell while keeping the original date value for calculations.
- Ctrl + 1: Opens the Format Cells dialog box to access all number formatting options.
- TEXT function: Converts a date to a text string showing the weekday, useful for combining with other text in a formula.
Understanding Excel Date Formatting
Excel stores dates as serial numbers. The number 1 represents January 1, 1900. Each subsequent day is one integer higher. This system allows Excel to perform date arithmetic. Formatting controls how this serial number is displayed. The default date format might show something like “3/14/2024”. Custom format codes instruct Excel to display parts of the date, like the day, month, or year, in specific ways. The “dddd” code is one of these instructions. It tells Excel to take the serial number and display the corresponding full weekday name, such as “Thursday”. Using this format does not change the cell’s underlying value, so formulas referencing the cell still use the correct date.
Steps to Apply the dddd Custom Format
You can format a single cell, a range, or an entire column. The process is the same.
- Select your date cells
Click on the cell or drag to select the range containing the dates you want to format. - Open the Format Cells dialog
Press Ctrl + 1 on your keyboard. This is the shortcut to open the Format Cells dialog box. You can also right-click the selected cells and choose Format Cells from the menu. - Navigate to the Number tab
In the Format Cells dialog, ensure the Number tab is selected. This tab contains all number formatting categories. - Choose the Custom category
In the Category list on the left, click on Custom. The right side of the dialog will show a Type field and a list of existing custom codes. - Enter or select the dddd format
In the Type field, you can type “dddd” directly. You may also see it in the list below. After typing or selecting it, the Sample area above will show a preview of how your selected cell will look. - Apply the format
Click the OK button. Your selected cells will now display the full weekday name, like “Monday” or “Friday”.
Using the TEXT Function for Weekdays
For situations where you need the weekday as text in a formula, use the TEXT function. This method is useful for creating dynamic labels or combining the weekday with other text in a single cell.
- Start a formula in a new cell
Click in an empty cell where you want the weekday text to appear. - Enter the TEXT function
Type =TEXT( and then click on the cell containing your date. Add a comma, then the format code in quotes: “dddd”. For example, if your date is in cell A1, the formula is =TEXT(A1, “dddd”). - Complete the formula
Press Enter. The cell will show the weekday name as a text string. You can combine it with other text, like =”Meeting on ” & TEXT(A1, “dddd”).
Common Mistakes and Formatting Limitations
Cell Shows #### After Formatting
If your cell shows hash symbols (####), the column is not wide enough to display the longer text of the weekday name. The underlying date value is still correct. To fix this, double-click the right border of the column header. This automatically resizes the column to fit the longest content.
Format Shows Numbers Instead of Weekday Names
If applying “dddd” shows a number like 45321, the selected cell does not contain a valid Excel date. It may contain a number formatted as General or text that looks like a date. First, ensure the cell value is a real date. You can test this by changing the cell’s format to General. If it shows a serial number, it’s a date. If it shows text or a different number, you need to convert it to a date using the DATEVALUE function or by using Data > Text to Columns.
Need the Abbreviated Weekday Name
The “dddd” code shows the full name. For a three-letter abbreviation like “Mon” or “Fri”, use the custom format code “ddd”. You apply it using the same steps in the Format Cells dialog, but type “ddd” in the Type field instead.
Custom Date Format Code Comparison
| Item | Format Code “dddd” | Format Code “ddd” | TEXT Function |
|---|---|---|---|
| Display Result | Full weekday name | Three-letter abbreviation | Weekday as text string |
| Cell Value Type | Remains a date number | Remains a date number | Becomes text, not a date |
| Use in Calculations | Yes, original date is unchanged | Yes, original date is unchanged | No, cannot be used in date math |
| Best For | Permanent display formatting in a data column | Tables where space is limited | Formulas that combine weekday with other text |
You can now display the day of the week for any date in your worksheet using the custom dddd format. Remember that the Ctrl + 1 shortcut is the fastest way to open the formatting dialog. For more control, try combining format codes, such as “dddd, mmmm d, yyyy” to display “Friday, March 15, 2024”. This keeps your data clean and functional for sorting and formulas.