Date to Weekday in Excel: Formulas and Formatting

Formulas & functions · Updated November 22, 2026

To turn a date into its weekday in Excel, use =TEXT(A2,"dddd") if the date is in A2; it returns the weekday name, such as Saturday. To keep the value as a date and simply show its weekday, format the cell with the custom number format dddd instead.

Two quick ways to show a weekday

Excel offers two approaches, depending on whether you need a weekday label or a date that remains usable in calculations:

  • Return a weekday name as text: Put the date in A2, enter =TEXT(A2,"dddd") in another cell, and press Enter. For March 8, 2025, the result is Saturday. Use ddd in place of dddd for the shorter label Sat.
  • Show the weekday while keeping the date: Select the date cell, press Ctrl+1 on Windows, choose Number > Custom, enter dddd in the Type box, and select OK. The cell displays Saturday, but its underlying value remains the date March 8, 2025.

The formula is useful when you need a separate label for a report, lookup, or export. Custom formatting is usually better when you want a date column to remain a date for sorting, filtering, or date arithmetic. Both methods update the displayed weekday if the date changes.

How Excel stores dates

Excel stores ordinary dates as serial numbers: whole-number values count days from a starting point, and any decimal portion represents time within a day. The cell’s number format determines whether the value appears as a date, a number, or a weekday name. This is why changing a date’s format can change its appearance without changing the date itself.

Weekday formulas calculate from the underlying date value, not from how the date looks on screen. A cell displayed as 8-Mar-2025 and one displayed as Saturday can therefore refer to the same date. If a cell contains text that merely looks like a date, however, Excel may not recognize it as a date value; that distinction is a frequent cause of errors.

Most current Windows workbooks use Excel’s 1900 date system. Excel also supports a 1904 date system, which can matter when dates are copied between certain older or Mac-created workbooks. If a copied date shifts by several years, check whether the workbooks use different date systems under File > Options > Advanced > When calculating this workbook. Do not change that setting casually in a working file: changing date systems can alter how existing dates are interpreted.

Use WEEKDAY when you need a number

For calculations, use WEEKDAY rather than TEXT. With the date March 8, 2025, in A2, enter =WEEKDAY(A2,2) in another cell; the result is 6, because this numbering scheme assigns Monday 1 through Sunday 7. This makes it convenient to test for a Monday-to-Friday workday or group records by weekday number.

The second argument, called return_type, controls the numbering:

  • 1 or omitted: Sunday is 1, Monday is 2, through Saturday 7.
  • 2: Monday is 1, Tuesday is 2, through Sunday 7.
  • 3: Monday is 0, Tuesday is 1, through Sunday 6.

For example, with March 8, 2025, in A2, =WEEKDAY(A2,1) returns 7 because Saturday is the seventh day in the Sunday-first system. Be explicit about the return_type instead of relying on the default: using different systems in different formulas can make tests and summaries inconsistent.

Choose the right method for your task

The choice is mainly about what the result needs to do after you calculate it. TEXT returns text; a custom date format changes only the display; WEEKDAY returns a number. These values behave differently in later formulas, so select the output that fits the next step.

  • Use custom formatting when a date should still sort chronologically and work in date calculations, but readers should see the weekday. The full date remains stored even if the display shows only Saturday.
  • Use TEXT when you need a separate weekday label in a report, a concatenated sentence, or a text export. Remember that its result is text, not a date.
  • Use WEEKDAY when a formula needs to compare, count, or classify weekdays. The numeric result is easier to test than a written label.

One practical layout is to keep the original date in column A and put a weekday name or number in column B. That preserves the source date for sorting and auditing. If you sort a column containing weekday names, Excel sorts the words alphabetically by default, not Monday-to-Sunday; sort by the original date or use a deliberate weekday-number order instead.

Find the start of the week

A related task is finding the Monday of the week containing a date. With a valid date in A2, enter =A2-WEEKDAY(A2,2)+1 in another cell. For March 8, 2025, the result is March 3, 2025. Format the result as a date if Excel displays a serial number.

This calculation uses WEEKDAY’s Monday-first numbering: subtracting the weekday number moves back to the prior Sunday, and adding one returns Monday. To display that result as a weekday name, apply the custom format dddd. If you instead need Sunday as the first day of the week, the calculation must use a Sunday-first numbering scheme; clarify what “start of the week” means for your calendar or report before building the formula.

Common problems and fixes

If the result is a number such as 45724 rather than a weekday, check what you entered. WEEKDAY is supposed to return a number; TEXT with a weekday format returns a name. A date cell accidentally formatted as General may also show its serial number. Apply a date format or the custom format dddd to make the display readable.

If Excel returns #VALUE!, or a formula seems unable to interpret the date, the source may be text. A value imported from a CSV can look like 3/8/2025 while remaining text, and the order of month and day can be ambiguous across regional settings. Check whether Excel recognizes the cell as a date by temporarily applying a standard date format or General. If it does not, convert the source using a method that matches its actual date order; for imported columns, Data > Text to Columns lets you choose a date format in the wizard.

Other useful checks include:

  • Confirm that the formula refers to the cell containing the date, not a blank or a heading.
  • Check for a leading apostrophe or spaces in imported values; these can make date-like content text.
  • Make sure the source contains a real date and not just a weekday word. Excel cannot infer a unique date from “Saturday” alone.
  • Verify the return_type in WEEKDAY if a weekend check appears reversed. Sunday is 1 with the default scheme but 7 with return_type 2.

Weekday names, language, and regional settings

Excel’s displayed weekday names can follow the language and regional settings in use. The TEXT formula’s format code dddd requests a full weekday name, but the resulting language may depend on the Excel environment. A workbook opened on another computer may therefore display localized labels, while a fixed text label produced for an export may need additional localization planning.

Date entry is also affected by regional conventions. For example, 3/8/2025 can mean March 8 or August 3 depending on the expected order. When sharing data across regions, prefer an unambiguous date source or confirm the import settings instead of assuming Excel will interpret every slash-formatted date the same way. The weekday calculation is only as reliable as the date value Excel receives.

These formulas work in Microsoft Excel for Microsoft 365 on Windows and in current Excel for Mac. On Mac, Command+1 opens Format Cells where Ctrl+1 is the Windows shortcut. Excel for the web supports date formulas and number formatting too, although the available menus and dialogs can differ from the desktop steps.

Frequently Asked Questions

How do I convert a date to a day name in Excel?
With a date in A2, enter =TEXT(A2,"dddd") in another cell for the full weekday name. Use ddd inside the format string for an abbreviated name.

How do I show the weekday without changing the date?
Select the date cell, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and set the custom number format to dddd. Excel keeps the underlying date value.

What does WEEKDAY return in Excel?
WEEKDAY returns a number for the day of the week. Its optional second argument sets the numbering; for example, return_type 2 numbers Monday as 1 and Sunday as 7.

Why does my weekday formula return a number?
If you used WEEKDAY, a number is the expected result. To get the written name, use TEXT with dddd, or apply that custom number format to the original date cell.

Can I get the weekday from a date stored as text?
First convert the text to a real Excel date using the source’s actual date order and regional convention. Data > Text to Columns can help with imported columns; a weekday formula may not work reliably on unrecognized text.

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.