Excel Weekday Name: Show the Day from Any Date in Excel

To show the weekday name for a date in Excel, put the date in A2 and enter =TEXT(A2,"dddd") in another cell. If A2 contains January 15, 2024, the result is Monday. Use "ddd" instead of "dddd" for the short form, Mon.

How Excel Gets the Day Name

Excel stores valid dates as numbers, with a date format controlling how those numbers appear in the grid. The TEXT function takes a value and a format code, then returns the formatted result as text. In this case, the date value is converted into the weekday label that corresponds to it.

For a simple example, assume A2 contains the real Excel date January 15, 2024, and you want the label in B2. Enter =TEXT(A2,"dddd") in B2; it returns Monday. The date is a Monday, so the displayed name is not dependent on what weekday the week is set to start on. The formula reads the date itself and returns its corresponding day.

The quotes around dddd are required because the format code is text inside the formula. The number of d characters controls the label’s length: four gives the full weekday name, while three gives an abbreviation. The output of TEXT is text, not another date value, which matters when you sort, calculate, or apply date formatting later.

Full Names and Short Names

Choose the format code that suits the space and purpose of your sheet. A calendar heading may need a short label, while a report or exported list may be clearer with the full name. The formula changes only in its format code.

dddd displays the full name, such as Monday.
ddd displays an abbreviated name, such as Mon.
d or dd is for the day of the month, not the weekday name. For example, a date on the fifteenth may display as 15.

With the date in A2, use =TEXT(A2,"ddd") to return Mon for January 15, 2024. This distinction between lowercase d and uppercase month codes is useful when building date formats: Excel uses d or dd for the day number, ddd or dddd for the weekday, and m or mm for the month number. A format such as "ddd, mmm d" can produce a compact date label like Mon, Jan 15.

Show a Weekday Without Converting the Date to Text

TEXT is convenient when you specifically want a text label in a separate cell. If you want the original date to remain a date value while merely changing how it looks, apply a custom number format instead. That keeps the underlying date available for date calculations, chronological sorting, and other date-based operations.

Select the date cells, press Ctrl+1 on Windows, choose the Number tab, select Custom, and enter dddd in the Type box. Click OK. The cell displays the weekday name, but its value is still the original date. To show a shorter name, use ddd as the custom format. On a Mac, open the Format Cells dialog from Format > Cells, then use the Number and Custom options; the format code is the same.

For example, if A2 holds January 15, 2024, and you apply the custom format dddd directly to A2, the cell shows Monday while still containing the date. A formula in another cell can still refer to A2 as a date. By contrast, =TEXT(A2,"dddd") in B2 creates the text value Monday in B2 and leaves A2 unchanged.

Get a Weekday Number When You Need One

Some tasks need a numeric weekday rather than a written name—for example, assigning a different rate to weekdays and weekends. The WEEKDAY function returns a number based on a date and a chosen numbering system. Its second argument sets which day is numbered first.

With the date in A2, enter =WEEKDAY(A2,2) to number Monday as 1 and Sunday as 7. January 15, 2024, returns 1 because it is a Monday. This numbering is often handy for workweek calculations, but it does not return a name. Use TEXT when the result should read Monday; use WEEKDAY when later logic needs a number.

Without a second argument, WEEKDAY uses a system where Sunday is 1 and Saturday is 7. That default can surprise someone expecting Monday to be the first day. Supplying the argument explicitly makes the intended convention clear. Other return types are available, so check the numbering scheme before using the result in a formula that identifies weekends or business days.

Apply the Formula to a List of Dates

For a column of dates, enter the weekday formula once and fill it down. With dates in A2:A20 and the weekday labels needed in B2:B20, enter =TEXT(A2,"dddd") in B2. Then copy B2 and paste it down through B20, or drag the fill handle at the lower-right corner of B2. Excel adjusts the reference for each row, so B3 refers to A3, B4 to A4, and so on.

To fill a formula down a selected range on Windows, select B2:B20 and press Ctrl+D after entering the formula in B2. In Excel for Mac, the equivalent fill-down shortcut is Control+D. The formula works in Excel for the web too, though available shortcuts can depend on the browser and keyboard settings; copying and pasting the formula is a dependable alternative.

If blank rows are mixed into the date list, a basic TEXT formula can display an unwanted result for an empty reference. To leave the corresponding output cell empty, with dates in A2:A20 and labels in B2:B20, use this in B2 and fill it down: =IF(A2="","",TEXT(A2,"dddd")). It returns an empty string when A2 is blank and the weekday name when A2 contains a date.

Common Problems and What to Check

The result is a number or an unexpected label. Confirm that the format code is in quotation marks and that you used four d characters for the full weekday name. A code of dd means day of the month, not weekday.
The formula shows an error or does not recognize the date. Check that the source cell contains a real Excel date rather than text that merely looks like one. Imported data can contain text dates, and recognition depends on their format and regional settings. Convert or re-import the values as dates before relying on date functions.
The weekday seems wrong. Verify the source date and whether it is stored as the intended calendar date. A date entered in a different regional order—for example, month/day versus day/month—can be interpreted differently than expected.
Sorting produces an odd order. A TEXT result is text, so sorting its labels alphabetically puts them in alphabetical order, not Monday-to-Sunday order. Sort by the original date column, or use a separate numeric weekday value if you need a weekday-based sort.
A blank source cell produces a label. Use the IF-wrapped formula above to return a blank when the source is empty.

For a quick check that a value is stored as a date, select it and temporarily apply a standard date format or inspect the formula bar. A genuine date can also be used in date arithmetic; a text string that resembles a date may not behave the same way. If the source is an imported text column, use Excel’s data-conversion tools or correct the import settings rather than trying to fix every weekday label individually.

Choose the Method That Fits the Sheet

Use TEXT when you need a separate, readable weekday label, such as in a summary table or a report column. Use a custom format when the cell should continue to behave as a date and only its display needs to change. Use WEEKDAY when a formula needs a numeric day identifier for conditions, grouping, or calculations.

There is no single best method for every workbook. A text label is easy to copy into a report, but it is not itself a date. A custom format preserves the date value, but another person may not notice that the cell is displaying a weekday instead of the full date. A numeric weekday is compact and useful for logic, but it is less immediately readable unless you document the numbering convention.

If you want both a useful date value and a visible weekday, keep the date in its own column and put the TEXT result in a neighboring column. That arrangement supports chronological sorting while making the weekday easy to scan. It also makes it clearer to other workbook users which cells hold dates and which hold labels.

Language and Regional Settings

The weekday name displayed by a date format may follow Excel’s language or regional formatting settings. A workbook opened on a computer configured for another language may therefore show a localized day name. The date itself can still be the same; it is the displayed label that changes according to formatting conventions.

If a report must always contain English weekday names, check the workbook’s display behavior in the environment where it will be used, especially if it will be shared internationally. Do not assume that a date’s appearance in one installation guarantees identical wording on every device. For most personal and local workbooks, the normal dddd format is the simplest choice.

Frequently Asked Questions

What is the Excel formula for the day name?
With a date in A2, use =TEXT(A2,"dddd") for the full weekday name. Use =TEXT(A2,"ddd") for an abbreviated name.

How do I get the weekday name without a formula?
Select the date cell, open Format Cells, and apply the custom number format dddd. The cell will show the day name while retaining its date value.

How do I show only Mon, Tue, or Wed?
Use the three-character date format code ddd. In a formula, with the date in A2, enter =TEXT(A2,"ddd").

Why does Excel show the wrong weekday?
Check whether the source is a real date and whether Excel interpreted its month and day in the intended order. Also make sure the date itself is correct; weekday numbering options affect numeric WEEKDAY results, not the calendar day that TEXT displays.

Can I use the weekday name to sort dates?
You can sort text labels alphabetically, but that will not create calendar order. Keep and sort by the original date, or create a numeric weekday helper value with WEEKDAY if a weekday-based order is needed.

Does the formula work in Excel for Mac and Excel for the web?
Yes. TEXT and WEEKDAY use the same formula syntax in current Excel for Windows, Mac, and the web. Cell-format dialog paths and keyboard shortcuts can differ, but the date format codes such as dddd remain the same.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.