Excel Weekday From Date: Formulas That Work

Formulas & functions · Updated November 18, 2026

To get the weekday for a date in Excel, use =WEEKDAY(A2,2) when the date is in cell A2; this returns 1 for Monday through 7 for Sunday. To show the weekday as a name instead, use =TEXT(A2,"dddd"), which returns a name such as Friday. The right choice depends on whether you need a number for calculations or a readable label.

Get a weekday number with WEEKDAY

In Microsoft Excel for Microsoft 365 on Windows, enter a date in one cell and the WEEKDAY formula in another. For example, put June 14, 2024, in A2. With the date in A2, enter =WEEKDAY(A2,2) in B2. The result is 5, because June 14, 2024, was a Friday and Monday is counted as 1.

The second argument, called the return type, sets which day starts the numbering. This matters because the same date can produce different weekday numbers with different settings. Excel’s common 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.
  • 3: Monday is 0, Tuesday is 1, and Sunday is 6.

For June 14, 2024, WEEKDAY with return type 1 gives 6, type 2 gives 5, and type 3 gives 4. Type 2 is often convenient for work schedules because the weekdays occupy numbers 1 through 5 and the weekend is 6 or 7. Choose a convention deliberately and use it consistently in related formulas; otherwise, a value such as 6 might mean Friday in one part of a workbook and Saturday in another.

Show the weekday as a name

If a worksheet is for people to read, a name is often clearer than a number. With the date in A2, enter =TEXT(A2,"dddd") in B2 to return the full weekday name, such as Friday. To display an abbreviated name such as Fri, use =TEXT(A2,"ddd") instead. Copy the formula down to get a label for each date in a list.

TEXT returns text, not a date value or a number. That is useful for labels, but it can matter later: text labels do not behave like date serial numbers in date arithmetic. Keep the original date column if you also need to sort chronologically, calculate durations, or use date-based criteria. In particular, sorting weekday names alphabetically will not put them in calendar order.

You can also display a weekday without adding a formula. Select the date cells, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter dddd as the format. Use ddd for the abbreviated form. This changes how the date appears, not the value stored in the cell: the cell remains a date that can be used in calculations. On Mac, Command+1 opens Format Cells. A custom format is a good choice when you want dates to remain dates and simply need their weekday names visible.

Choose a numbering convention for your task

The best return type depends on what the result will do. Type 2 is usually easy to interpret for a Monday-to-Friday workweek. Type 1 matches a Sunday-first calendar convention. Type 3 can be useful when a zero-based sequence is specifically needed, but it may be less intuitive to readers who expect weekday numbers to start at 1.

For example, if dates are in A2:A20 and you want to flag weekend dates, type 2 makes the test straightforward: Saturday and Sunday return 6 and 7. With a date in A2, the formula =WEEKDAY(A2,2)>5 returns TRUE for a weekend date and FALSE for a weekday. This is a logical test, not a display label, so it can be used in other formulas or as a condition in a rule. If you use type 1 instead, Saturday and Sunday are 7 and 1, so a simple greater-than test would not identify both weekend days.

Weekends are not universal in every schedule. Some countries, workplaces, and industries use a different weekly rest pattern. WEEKDAY reports the calendar day according to its numbering system; it does not know which days your organization treats as nonworking. For a schedule with a nonstandard weekend, define the weekend days explicitly rather than assuming that Saturday and Sunday are always the only days off.

Understand how Excel dates work

Excel normally stores a date as a serial number and displays that number using a date format. The serial represents a day in Excel’s date system, so date calculations can add or subtract days. WEEKDAY reads the date value and determines its position in the seven-day week; it does not rely on how the date looks on screen. A cell formatted to display as 14-Jun-2024 and the same date displayed as June 14, 2024 can therefore produce the same weekday.

This distinction helps explain a common surprise: changing a cell’s format does not repair an invalid date. If a cell contains text that resembles a date, Excel may display it in a date-like way without treating it as a usable date serial. A weekday formula can then return an error or an unexpected result. Conversely, if a date is valid but displayed as a number, changing its number format to a date format may be all that is needed.

Excel workbooks can use the 1900 or 1904 date system, and that setting affects the serial numbers used for dates. A workbook’s setting is under File > Options > Advanced, in the section titled When calculating this workbook. Dates can shift when values are copied between workbooks that use different date systems. This is mainly a concern when transferring raw date serials or comparing workbook data, rather than for ordinary weekday formulas within one workbook. If imported dates look offset by several years, check the source and destination workbook settings before trying to correct individual cells.

Fix text dates and other common problems

If WEEKDAY does not return the expected answer, first check whether the input is a genuine Excel date. Select the cell and inspect the formula bar and number format. A quick test is to change the cell’s format temporarily to General: a valid date usually displays as a number. That number is not an error; it is the stored date serial. If the value remains text, conversion may be necessary.

For a date string Excel can recognize under the computer’s regional settings, and with that text in A2, =DATEVALUE(A2) converts it to an Excel date serial. You can then apply a date format or use WEEKDAY on the converted value. DATEVALUE depends on how the text is written and the locale settings: a string such as 03/04/2024 can mean March 4 or April 3 in different regions. Do not convert ambiguous date text in bulk until you have confirmed its intended order. For consistently structured imported data, using Excel’s import tools and specifying the date format is safer than guessing.

Other common causes of an unexpected result include:

  • A missing or wrong return type: the default starts the week on Sunday, so the result may differ from a Monday-first expectation.
  • A blank or invalid input: a blank reference may be treated like a zero in some formula contexts, while text or an invalid date can cause an error. Check the source cell rather than assuming the weekday result is meaningful.
  • A date with a time: Excel stores a time as a fractional part of a day. WEEKDAY uses the calendar date portion, so times during the same day do not change the weekday.
  • A formula showing literally: if the cell displays the formula instead of its result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Set the cell to General, re-enter the formula, and check the formula display setting.

Use weekday results in practical worksheets

A weekday number can support more than a label. It can help classify dates, check a schedule, or build a summary by day of the week. For a list of transactions, for example, a helper column with Monday-first numbers can make it possible to filter for weekends or group records by weekday. Keep the actual dates alongside that helper column so the workbook remains easy to audit.

For a readable weekday column that also stays connected to the source dates, put dates in A2:A20 and enter the weekday-name formula beside the first date, then fill it down. When adding new rows, ensure the formula is copied into them; an Excel table can help propagate calculated-column formulas as rows are added. When reviewing the output, compare a few known dates against a calendar, especially if the data came from an import or another workbook.

For a simple weekend check, with a date in A2 and Monday-first numbering, =WEEKDAY(A2,2)>5 gives TRUE for Saturday or Sunday. You could use that logical result in a separate status column or as the basis for conditional formatting. If your weekend differs, write a condition suited to those specific days and document the rule so other users understand it.

WEEKDAY compared with related date tools

WEEKDAY answers “which day of the week is this date?” It does not return the week number of the year. Excel’s ISOWEEKNUM function returns an ISO week number, which is a different value and follows ISO week-year rules. A date near New Year’s Day can belong to an ISO week-year different from its calendar year, so do not substitute a week number when you need a weekday.

Likewise, WORKDAY and NETWORKDAYS address workday calculations rather than weekday identification. WORKDAY calculates a date a specified number of working days before or after another date, while NETWORKDAYS counts working days between dates, with options for holidays. Those functions assume a standard weekend unless you use their international variants with a weekend setting. Use WEEKDAY to identify or test the weekday itself; choose a workday function when the question is about business-day movement or counts.

A formula-based name and a cell format also serve different purposes. TEXT creates a separate text result, which can be useful in a report or concatenated label. A custom number format keeps a real date in the cell and changes only its display. If the result must be used in further date arithmetic, retaining the date value and applying a custom format is generally more flexible.

Frequently Asked Questions

How do I get the day name from a date in Excel?
With the date in A2, use =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for an abbreviation. Alternatively, apply the custom number format dddd to the date cell to show its name without changing the stored date.

What does WEEKDAY return by default?
If you omit the optional return type, WEEKDAY uses type 1: Sunday is 1, Monday is 2, and Saturday is 7. Add a return type such as 2 when you want Monday to be 1.

How can I check whether a date is a weekend?
With a date in A2, =WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday. This assumes a Monday-to-Friday workweek. Adapt the test if your schedule treats different days as weekends.

Why does my weekday formula return an error?
The referenced cell may contain text rather than a valid Excel date, or it may contain an unrecognized date string. Check the source value and regional date order. For recognizable date text in A2, DATEVALUE can convert it, but ambiguous strings should be interpreted explicitly before conversion.

Can Excel show the weekday without a formula?
Yes. Select the date cell, open Format Cells, and choose a custom format of dddd or ddd. The displayed weekday changes, but the underlying date remains available for sorting and calculations.

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.