Date to Weekday Formula in Excel: WEEKDAY and TEXT

Formulas & functions · Updated November 22, 2026

To convert a date into its weekday number in Excel, use =WEEKDAY(A2,2), assuming the date is in cell A2; Monday returns 1 and Sunday returns 7. To show the weekday name instead, use =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for an abbreviation.

Choose the formula for the result you need

The right date-to-weekday formula depends on whether you need a number, a readable label, or just a different display for the original date. WEEKDAY returns a number that is useful for calculations, filters, and rules. TEXT returns text such as Tuesday, which is convenient in reports but is not a numeric weekday value.

  • Number for calculations: use WEEKDAY with a return type that sets the first day of the week.
  • Full weekday name: use TEXT with the format code "dddd".
  • Short weekday name: use TEXT with "ddd", which usually displays a three-letter abbreviation such as Tue.
  • Display the weekday without adding a formula: apply a custom date format of dddd to the date cells.

For most schedules that treat Monday as the first day of the week, =WEEKDAY(A2,2) is a dependable starting point. If you only want a label for a chart or report, =TEXT(A2,"dddd") is usually simpler.

How the WEEKDAY function works

WEEKDAY reads a valid Excel date and returns an integer representing the day of the week. Its basic structure is WEEKDAY(serial_number,[return_type]): the first argument is the date, and the optional second argument controls the numbering system. If you leave out the second argument, Excel uses return type 1.

For example, suppose A2 contains August 20, 2024, a Tuesday. With the date in A2, =WEEKDAY(A2,2) returns 2, while =WEEKDAY(A2) returns 3 under the default Sunday-first numbering. The date is the same; only the numbering convention changes. This matters when formulas compare weekday numbers, such as distinguishing weekdays from weekends.

Excel stores recognized dates as serial values and displays them using a date format. WEEKDAY uses the underlying date value, not the way the date happens to look in the cell. A cell displaying 20-Aug-2024 and one displaying 8/20/2024 can therefore produce the same weekday result if both contain the same date.

Return types and which day starts the week

The second argument is often called the return type. Choose it deliberately: using a different type can change the number returned without changing the date. The most commonly useful options are:

  • 1, or omitted: Sunday is 1, Monday is 2, and Saturday is 7.
  • 2: Monday is 1, Tuesday is 2, and Sunday is 7. This is a convenient Monday-first system.
  • 3: Monday is 0, Tuesday is 1, and Sunday is 6. This can be helpful when a calculation needs Monday to have a zero offset.

Excel also accepts return types 11 through 17. They all number the days from 1 to 7, but start with a different day: 11 starts Monday, 12 Tuesday, 13 Wednesday, 14 Thursday, 15 Friday, 16 Saturday, and 17 Sunday. For a typical Monday-to-Sunday work calendar, type 2 is easier to recognize than these alternatives.

To test whether a date falls on Saturday or Sunday with Monday numbered 1, compare the result with 5. With the date in A2, =WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday and FALSE for Monday through Friday. This is a logical test, not a weekday name; use it inside IF if you want a text label.

Show the weekday name with TEXT

TEXT converts a number or date into text using a format code. For a date in A2, =TEXT(A2,"dddd") returns the full weekday name, such as Tuesday. The shorter code "ddd" generally returns an abbreviated name, such as Tue. The formula result is text, so it is easy to read but does not behave like a numeric date in later date calculations.

You can combine the weekday label with other text when preparing a display column. With the date in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns a result such as Tuesday, August 20, 2024. That is useful for a human-readable report, but it is still a text string. Keep the original date in a separate column if you need to sort chronologically, calculate elapsed time, or use date filters reliably.

Weekday names shown by TEXT can depend on Excel’s language and regional settings. The format code specifies the date component, but Excel may display the name in the language associated with the environment. If a workbook is shared with people using different language settings, check how the labels appear for the intended audience.

Apply a weekday display without a formula

If the goal is only to make existing dates show their weekday names, change their number format rather than creating a separate formula column. This preserves the underlying date, so the cells can still be used in calculations and sorted as dates.

  1. Select the date cells.
  2. On Windows, press Ctrl+1 to open Format Cells. On Mac, press Command+1.
  3. Open the Number tab, select Custom, and enter dddd in the Type box for the full name. Enter ddd for an abbreviation.
  4. Select OK to apply the format.

The displayed value may become Monday or Mon, but the cell still contains the original date. To see both the date and weekday, a custom format such as ddd, mmm d, yyyy can show a compact label while retaining the date value. Formatting changes appearance; it does not replace the date with weekday text.

Use weekday results in other formulas

A numeric weekday result can drive practical checks. For example, a schedule may need a visible Weekend or Weekday label. With a valid date in A2 and Monday-first numbering, =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") returns Weekend for Saturday and Sunday and Weekday for Monday through Friday. The comparison works because the weekend values are 6 and 7 in this numbering system.

You can also use a weekday number to group or summarize dates, but keep the numbering convention consistent throughout the workbook. A formula that assumes Monday equals 1 will misclassify days if another formula or lookup uses the default Sunday-first type. For schedules with nonstandard weekends, such as a workweek that runs Sunday through Thursday, a simple greater-than-5 test is not suitable; define the actual weekend days in the logic instead.

When a report needs both a friendly name and a calculation-ready value, use separate columns: one for the date, one for WEEKDAY, and optionally one for the TEXT label. This makes the workbook easier to audit and avoids trying to perform arithmetic on names such as Wednesday.

Common problems and how to fix them

If WEEKDAY returns an error or an unexpected result, first check what is actually stored in the date cell. A date that looks valid may be text imported from another system. Excel can calculate with recognized date serials, but text that resembles a date may not be interpreted consistently across regional settings.

  • Check whether the cell contains a number: with the suspected date in A2, =ISNUMBER(A2) returns TRUE when Excel stores the value as a number, as it normally does for a genuine date.
  • Check the return type: a Tuesday is 2 with type 2 but 3 with the default type 1. That difference is expected, not an error.
  • Check the cell reference: confirm that the formula points to the intended date, particularly after copying it down a table.
  • Inspect imported dates: text dates may need to be converted or parsed using the correct regional date order. For instance, 04/05/2024 can mean April 5 or May 4 depending on the source convention.

Changing a text cell’s number format alone does not necessarily convert it into a real date. If the source data is text, use a suitable import or conversion method that matches its order and locale, then verify the resulting date before relying on weekday calculations. Avoid guessing when day and month can be confused.

Frequently Asked Questions

What is the formula to get the day of the week from a date in Excel?
With the date in A2, use =WEEKDAY(A2,2) for a number where Monday is 1 and Sunday is 7. Use =TEXT(A2,"dddd") when you want the weekday name instead.

How do I make Monday equal 1 in Excel?
Supply 2 as WEEKDAY’s second argument. With a date in A2, =WEEKDAY(A2,2) numbers Monday through Sunday from 1 to 7. Without that argument, Excel uses Sunday as 1.

How can I show only Mon, Tue, or Wed?
With a date in A2, use =TEXT(A2,"ddd") to return an abbreviated weekday label. Alternatively, apply the custom number format ddd to the original date cells if you want to change only their display.

Does TEXT change my date into a weekday-only value?
TEXT returns text, not a date value. The original date remains unchanged if the formula is in another cell, but the formula result itself is a label. Keep a real date column for sorting and calculations.

Why does my weekday number differ from someone else’s?
The formulas may use different return types. For the same date, WEEKDAY with type 1 numbers Sunday as 1, while type 2 numbers Monday as 1. Compare the second argument before treating the results as inconsistent.

Quick recommendation

For ordinary Monday-first weekday numbering, use WEEKDAY with return type 2. For a name, use TEXT with "dddd" or "ddd"; for a display-only change, apply a custom number format. Confirm that the source cell is a real Excel date and that the chosen week-start convention matches the calendar or business rule behind your workbook.

Related

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.