Excel Day Name Formula: Show the Weekday from a Date

To show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains a real Excel date; for April 15, 2024, the result is Monday. Use ddd instead of dddd for an abbreviated name such as Mon.

The quickest way to display a day name

The TEXT function converts a value into text using a format you specify. In this case, the date remains in A2, while the formula displays its weekday in a separate cell. This is useful for adding a readable day column to a schedule, attendance list, project plan, or transaction table.

For a typical worksheet with dates in column A, enter the formula in B2, then copy it down beside the dates. The formula refers to A2, so Excel adjusts the reference to A3, A4, and so on when it is filled down. If your dates are in a different column or row, replace A2 with the cell that contains the date you want to label.

=TEXT(A2,"dddd") displays the full weekday name, such as Monday.
=TEXT(A2,"ddd") displays an abbreviation, such as Mon.
=TEXT(A2,"ddddd") is not the standard way to request a longer weekday name; use four d characters for the full name.

The formula changes only what appears in the formula cell. It does not replace or modify the original date.

How the formula works

Excel stores valid dates as numeric serial values and formats those values so people can read them as dates. TEXT takes the date value and returns a text string according to its format code. The four-letter code dddd means to display the full weekday name; the three-letter code ddd requests the abbreviated form.

With April 15, 2024 in A2, =TEXT(A2,"dddd") returns Monday, while =TEXT(A2,"ddd") returns Mon. With April 16, 2024 in A2, those formulas return Tuesday and Tue. The date in A2 is still available for calculations, comparisons, and sorting, even though the formula result is text.

The weekday name is based on the date value, not on the cell’s current display format. A2 might appear as 4/15/2024, 15-Apr-2024, or another date style; the same TEXT formula can return Monday in each case as long as Excel recognizes the cell as a date.

Full names, abbreviations, and examples

Choose the format code that suits the space available and the reader. A full weekday name is clearer in reports, while an abbreviation can keep a compact calendar or dashboard from becoming too wide.

For a table with dates in A2:A20 and day names in B2:B20, put =TEXT(A2,"dddd") in B2 and fill it down. If A2 is April 15, 2024, B2 displays Monday.
For the same layout, put =TEXT(A2,"ddd") in B2 if you prefer short labels. If A2 is April 15, 2024, B2 displays Mon.
For a single date in D5, use =TEXT(D5,"dddd") in E5. If D5 contains May 1, 2024, E5 displays Wednesday.

These examples assume that each source cell contains an actual Excel date. They also show why a formula should reference the source date cell rather than type a weekday name manually: the result updates automatically when the date changes.

Show a day name without creating text

If the goal is only to change how a date looks, a custom number format may be better than a formula. Formatting keeps the cell’s underlying value as a date, so it can still be used directly in date arithmetic and chronological sorting. The displayed text can still look like a weekday.

Select the date cells you want to format.
In Excel for Microsoft 365 on Windows, press Ctrl+1 to open Format Cells. On Mac, press Command+1.
Choose the Number tab, select Custom, and enter dddd in the Type box.
Select OK. The cells display weekday names, but their stored date values remain unchanged.

For example, if A2 contains April 15, 2024, applying the custom format dddd makes A2 display Monday. The formula method would instead put the word Monday in a separate cell. Use formatting when the same date cell should remain a usable date but be presented as a weekday; use TEXT when you need a separate text label for a report, concatenation, or export.

Custom formatting does not remove the date. If you click the cell, the formula bar still shows its date value. To show both the weekday and the date in one cell, a custom format such as dddd, mmmm d, yyyy can display Monday, April 15, 2024 while preserving the date underneath.

Return a weekday number instead

Sometimes a number is more useful than a name, such as when you need to group, filter, or calculate by weekday. WEEKDAY returns a number based on a numbering system you select. The second argument controls which day counts as the first day of the week.

With the date in A2 and Monday as day 1, use =WEEKDAY(A2,2). For April 15, 2024, this returns 1; for April 21, 2024, a Sunday, it returns 7. This Monday-to-Sunday numbering is often convenient for workweek schedules.

With the date in A2 and the default Sunday-first numbering, use =WEEKDAY(A2,1). April 15, 2024 returns 2 because Sunday is 1 and Monday is 2. Choosing the correct return type matters: the result is a number, and different return types assign different numbers to the same date.

If you need a name from a number rather than a TEXT format, CHOOSE can map weekday numbers to labels. With the date in A2 and Monday-first numbering, use =CHOOSE(WEEKDAY(A2,2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"). For April 15, 2024, it returns Monday. The labels in that formula are typed in English, so change them if your workbook needs another language. TEXT generally needs less maintenance for a simple weekday label.

Fill the formula down a date list

For a short list, enter the formula beside the first date and drag the fill handle—the small square at the lower-right corner of the selected cell—down the column. You can also copy the formula and paste it into the remaining cells. Excel adjusts the relative reference automatically, so B3 will refer to A3 after B2’s formula is filled down.

For a continuous table, place the formula in the first row of the adjacent column. Excel tables often copy a formula down the calculated column as you enter it, and can extend that formula to new table rows. Check the first and last results after filling: a blank source date or an incorrectly referenced cell can produce unexpected output.

If you want the weekday to appear in the same cell as the date, do not put a TEXT formula over the date itself. A formula cannot replace its own input without creating a circular reference. Use a separate formula column, or apply the custom date format described above.

Common problems and how to fix them

The formula displays a number or an error. Confirm that the referenced cell contains a valid Excel date, not text that merely looks like one. A date imported from a website or CSV can be stored as text. Re-enter it as a date or convert the source data to real dates before relying on TEXT.
The result is in another language. Excel’s date text and formatting can reflect the Office or regional language settings. If the workbook is opened under a different locale, a displayed weekday may be localized. If the output must always use fixed English labels, a CHOOSE mapping with English names is one option.
The weekday seems one day off. Check the source date and confirm that the cell is not text interpreted under a different day-month order. For example, an imported value such as 04/05/2024 can mean April 5 or May 4 depending on the source convention and settings.
Sorting puts days in an unexpected order. TEXT returns words, so a column of names may sort alphabetically rather than Monday through Sunday. Sort by the original date, or add a numeric helper using WEEKDAY with a chosen return type.
The result does not update as expected. Verify that the formula points to the intended date cell. When copying formulas, relative references move; add dollar signs to lock a reference only when you genuinely need the same source cell in every row.

A common misconception is that changing the cell display to dddd changes the date into a weekday string. It does not: custom formatting changes appearance while retaining the date value. TEXT does return text, which is why it behaves differently in later calculations and sorting.

Which method should you choose?

Use =TEXT(A2,"dddd") when you need a weekday name in a separate cell that can update with the source date. Choose =TEXT(A2,"ddd") when a short label is easier to fit. Choose a custom number format when the date itself should remain the cell’s value but appear as a weekday, perhaps alongside the full date.

Use WEEKDAY when you need a numeric result for formulas, grouping, or weekday-based logic. It is usually a better helper value for sorting days in calendar order than a text label. CHOOSE is useful when you require explicitly specified names, but its list must be maintained if you change the numbering order or language.

One practical layout is to retain the real date in column A, show a readable weekday in column B, and keep any weekday number in a hidden or helper column only if another calculation needs it. This separates the original data from the presentation and makes the workbook easier to check.

Frequently Asked Questions

What is the Excel formula for the day name? If the date is in A2, use =TEXT(A2,"dddd") for the full weekday name. The date must be a real Excel date for the result to be reliable.

How do I get only the first three letters of a weekday? With the date in A2, use =TEXT(A2,"ddd"). It returns an abbreviation such as Mon for Monday; the exact localized abbreviation can depend on language settings.

Can Excel show the weekday in the same cell as the date? Yes. Apply a custom number format such as dddd to the date cell, or use a format such as dddd, mmmm d, yyyy to show the weekday and date together. The underlying value remains a date.

Why does TEXT return the wrong weekday or an error? The source may be text rather than a genuine date, or an imported date may have been interpreted using the wrong month-and-day order. Check the source value and its regional date convention, then convert it to a valid Excel date.

How can I sort weekdays from Monday to Sunday? Sorting weekday names as text may arrange them alphabetically. Sort by the original date, or use a helper formula such as WEEKDAY with return type 2 to assign Monday 1 through Sunday 7, then sort by that number.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.