Excel Day of Week Formula: Return a Name or Number

If the date is in cell A2, use =WEEKDAY(A2,2) to return its weekday number, with Monday as 1 and Sunday as 7. To return the weekday’s name instead, use =TEXT(A2,"dddd"); for example, a date that falls on Sunday displays as Sunday.

Choose a formula for the result you need

Excel can identify a date’s day of the week in two useful ways: as a number or as a name. Choose the numeric result when you plan to compare weekdays, group records, or build other calculations. Choose a name when the result is mainly for people to read. Both formulas depend on Excel recognizing the source value as a real date, not merely text that looks like one.

With the date in A2, this formula returns a weekday number from 1 through 7, where Monday is 1 and Sunday is 7: =WEEKDAY(A2,2). With the same date in A2, this formula returns the full weekday name as text: =TEXT(A2,"dddd"). Enter the formula in another cell, such as B2, and press Enter. To apply it to more rows, copy or fill the formula down alongside the dates.

For example, if A2 contains June 15, 2025, the date is a Sunday. The numeric formula returns 7, while the name formula returns Sunday. Excel stores dates as serial values, so the formulas work from the underlying date value rather than from how the date happens to look on the worksheet.

How the WEEKDAY function works

WEEKDAY returns a number representing the day of the week for a date. Its second argument, called return_type, sets which day counts as the start of the week and which number represents it. Including that argument is important: it makes the numbering convention explicit rather than relying on a default.

With the date in A2, =WEEKDAY(A2,2) uses Monday-based numbering. The common return_type choices are:

2: Monday is 1, Tuesday is 2, through Sunday as 7. This is convenient for weekday calculations and weekend checks.
1: Sunday is 1, Monday is 2, through Saturday as 7. This matches a Sunday-first calendar convention.
3: Monday is 0, Tuesday is 1, through Sunday as 6. This can be useful when calculations need a zero-based offset from Monday.

For a Sunday date in A2, =WEEKDAY(A2,1) returns 1, whereas =WEEKDAY(A2,2) returns 7 and =WEEKDAY(A2,3) returns 6. The date itself has not changed; only the numbering system has. If you use a weekday number in a later formula, keep the same return_type convention throughout that calculation.

Return a weekday name with TEXT

The TEXT function formats a value as text using a format code. With a date in A2, =TEXT(A2,"dddd") displays the full weekday name, such as Wednesday. With the same cell layout, =TEXT(A2,"ddd") displays an abbreviated name, such as Wed. These are text results, not date values.

That distinction matters when you sort or calculate. A formula returning weekday names may sort alphabetically—Friday, Monday, Saturday—instead of in calendar order. It also cannot be used as a date for date arithmetic. If you need to sort records Monday through Sunday, retain a numeric weekday column using =WEEKDAY(A2,2) and sort by that number; the name can remain in a separate display column.

Names may follow Excel’s language and regional settings. A workbook opened with different language settings can display localized day names. If a report needs a fixed language regardless of the user’s settings, consider using a numeric weekday value as the stable result and apply labels deliberately, rather than assuming a text-format formula will always use English.

Display the weekday without changing the date

If you only want a date cell to look like a weekday, formatting the original cell can be simpler than adding a formula. The cell remains a date, so it can still be sorted chronologically, used in date calculations, or displayed later with a different format.

Select the date cells.
Open Format Cells with Ctrl+1 on Windows. In Excel for Mac, use Command+1.
Choose the Number tab, select Custom, and enter dddd in the Type box for a full weekday name, or ddd for an abbreviation.
Select OK. To show both the date and weekday, use a custom format such as ddd, mmm d, yyyy.

Custom formatting changes only the display, not the stored date. A cell formatted as dddd may visibly show Monday, but a formula that refers to it still uses the underlying date serial. By contrast, =TEXT(A2,"dddd") creates a separate text result. Use formatting when the date itself should remain the working value; use TEXT when you specifically need a weekday-name string in another cell or in a combined text label.

Check that Excel recognizes the source as a date

A date can look correct and still be stored as text, especially after copying data from a website, importing a CSV file, or combining information from different systems. In that case, WEEKDAY or TEXT may return #VALUE! because Excel cannot interpret the cell as a date value. A quick clue is that the entry may be left-aligned like other text, although alignment alone is not a definitive test because formatting can override it.

Select the source cell and inspect its formula bar and number format. Changing the format to a date format does not necessarily convert text into a real date; it may only change how a value is displayed. Re-entering the date in a form Excel recognizes, or using the import or data-cleaning tools appropriate to the source, can resolve the issue. Date interpretation can vary by regional settings: 04/05/2025 may mean April 5 or May 4, so verify the intended order before relying on results.

Blank cells deserve attention too. WEEKDAY applied to a blank reference can return a number that looks like a valid weekday, which may create misleading output in an otherwise empty table. With a date in A2 and a result formula in B2, use =IF(A2="","",TEXT(A2,"dddd")) to leave B2 empty when A2 is empty and show the name when it contains a date. This checks for an empty cell; it does not correct text dates or other invalid values.

Use weekday numbers for weekend logic

A numeric weekday is particularly useful when a worksheet needs to classify dates. With a date in A2 and Monday-based numbering, =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") returns Weekend for Saturday or Sunday and Weekday for Monday through Friday. The comparison works because return_type 2 assigns 6 and 7 to Saturday and Sunday.

For example, if A2 contains June 15, 2025, =WEEKDAY(A2,2) returns 7, so the classification formula returns Weekend. If the workbook treats Friday and Saturday as nonworking days, the same test is not appropriate: you must define the relevant weekend pattern rather than assuming every organization uses Saturday and Sunday. A weekday formula identifies the day; it does not know a company’s shift schedule, holidays, or local workweek.

For a simple weekday label, the IF formula is easy to audit. For more detailed scheduling, separate the date identification from the business rules. That makes it clearer whether a date is Saturday, a public holiday, or a special closure day instead of collapsing all exceptions into one Weekend label.

Copy the formula down and keep references consistent

When dates are listed in A2:A20, enter a formula in B2 and fill it down to B20. Excel adjusts the relative reference automatically: the formula beside A3 refers to A3, and the one beside A4 refers to A4. This is useful for a growing list, but check the first and last few results after filling to catch blank rows, imported text, or an accidentally shifted starting cell.

For a day-name column, with each row’s date in column A, enter =TEXT(A2,"dddd") in B2 and copy down. For a numeric helper column using Monday as 1, enter =WEEKDAY(A2,2) in B2 and copy down. You can sort the complete table by the numeric helper column to order weekdays Monday through Sunday; select the full data range when sorting so the dates and their other row information stay together.

If your data is an Excel table, entering the formula in the first row of a table column typically fills the calculated column for the table’s rows. Table formulas may use structured references instead of A2-style references, but the underlying WEEKDAY and TEXT functions work the same way. Avoid sorting only the weekday results by themselves, since that can separate them from their corresponding dates.

Frequently Asked Questions

What is the simplest Excel formula to show the day of the week?
With a date in A2, use =TEXT(A2,"dddd") for the full name, such as Thursday. Use =TEXT(A2,"ddd") for a short name, such as Thu.

How do I make Monday equal 1 and Sunday equal 7?
With the date in A2, use =WEEKDAY(A2,2). The second argument, 2, explicitly selects Monday-first numbering.

Can Excel show the weekday in the same cell as the date?
Yes. Apply a custom number format such as ddd, mmm d, yyyy to the date cell. This displays the weekday and date together while preserving the underlying value as a date.

Why does my weekday formula return #VALUE!?
The referenced cell may contain text that looks like a date but is not recognized as a date value. Check how the data was imported, confirm the intended date order, and convert or re-enter the value in a form Excel recognizes.

Why does my weekday name sort alphabetically?
A name returned by TEXT is text, so a normal text sort can put the names in alphabetical order. Use a helper column with =WEEKDAY(A2,2) and sort the complete table by that numeric column for Monday-to-Sunday order.

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.