Excel WEEKDAY Formula Not Working? Fix Common Errors

If Excel’s WEEKDAY formula is giving an error or the wrong day number, first confirm the referenced cell contains a real Excel date, then choose the numbering system you need. With a date in A2 and Monday as day 1, enter =WEEKDAY(A2,2); for Monday, January 15, 2024, the result is 1.

What the WEEKDAY function returns

WEEKDAY converts a date into a number representing its position in the week. It does not return a written day name by default, and it does not decide whether a date is a workday or a holiday. Its two arguments are a date serial (often a cell reference) and an optional setting that determines which day is numbered 1.

Excel stores valid dates as serial numbers, so a date displayed as January 15, 2024 is still a number underneath. WEEKDAY reads that date value and returns an integer. The cell’s display format does not normally change the underlying date, but text that merely looks like a date may not be recognized as one. That distinction explains many apparent formula failures.

If the second argument is omitted, Excel uses a Sunday-first numbering system. That default is a frequent source of “wrong answer” reports: a Monday returns 2, not 1. Decide what the number should mean before changing the formula or the date format.

Check the return type and numbering system

The second argument, often called return_type, controls the weekday numbering. The most useful choices are:

1 or omitted: Sunday is 1, Monday is 2, and Saturday is 7.
2: Monday is 1 and Sunday is 7. This is a common choice for Monday-first schedules.
3: Monday is 0 and Sunday is 6.
11 through 17: These start the week on Monday through Sunday, respectively. For example, 11 starts with Monday and 17 starts with Sunday.
21: Uses ISO weekday numbering, with Monday as 1 and Sunday as 7.

With the date January 15, 2024 in A2, the formula =WEEKDAY(A2) returns 2 because the default treats Sunday as 1. With the same cell and date, =WEEKDAY(A2,2) returns 1. Both results are correct under their respective numbering systems. If a formula is one number higher or lower than expected, check this setting before assuming the date is wrong.

When matching an external system, check its convention rather than relying on the word “weekday.” Some calendars count Sunday first, while many work schedules and ISO-based reports count Monday first. A formula can be functioning correctly and still disagree with a report that uses another convention.

Make sure the cell contains a real date

A cell may display something date-like while containing text. This can happen after copying data from a website, importing a CSV, or entering a date in a format Excel does not recognize under the computer’s regional settings. WEEKDAY cannot reliably calculate from arbitrary text, and may return #VALUE!.

To check the value, select the date cell and look at the formula bar. You can also test whether Excel sees A2 as a number. With the suspected date in A2, enter =ISNUMBER(A2) in another cell. A result of TRUE indicates that A2 is numeric, as a normal Excel date should be; FALSE suggests it is text or another nonnumeric value. This test does not prove that the number represents the intended date, so verify the displayed date too.

If A2 contains text such as 1/15/2024 and your Excel regional settings use month/day/year, DATEVALUE may convert it. With that text in A2 in a U.S. date format, enter =WEEKDAY(DATEVALUE(A2),2); it returns 1 because January 15, 2024 was a Monday. DATEVALUE depends on date conventions and may interpret or reject ambiguous text differently in other regions. For example, 04/05/2024 can mean April 5 or May 4. Confirm the intended order before converting a column of imported dates.

For data you control, a more dependable approach is to build dates from separate year, month, and day values, or correct the import so Excel recognizes the dates as dates. Changing a cell’s number format alone does not convert text into a date; it changes how an existing numeric value is displayed.

Return a weekday name instead of a number

WEEKDAY returns a number even if the result cell is formatted as a date. If you want a label such as Monday, use the TEXT function. With a real date in A2, enter =TEXT(A2,"dddd") to return the full weekday name; =TEXT(A2,"ddd") returns an abbreviated name such as Mon. For January 15, 2024, the full-name version returns Monday.

The language of the displayed name can depend on Excel’s language and regional settings. Also, TEXT returns text, not a date value. That is convenient for labels and reports, but the result is less useful for date arithmetic, chronological sorting, or calculations that expect a true date. Keep the original date in its own column when later calculations may need it.

If you only need a visible weekday name, another option is to keep the date value and apply a custom number format. Select the date cells, press Ctrl+1 on Windows, choose Number, then Custom, and use dddd as the format. The cell continues to hold the date, although it displays the weekday. On Mac, Command+1 opens Format Cells. This is a display change; a formula referencing the cell still receives the date serial.

Understand common errors and unexpected results

#VALUE! usually means Excel cannot use the supplied date argument as a valid date or number. Check whether the source is text, contains extra spaces, or includes an unrecognized date format. If the date came from an import, inspect several rows: a column can contain a mixture of real dates and text, making only some formula results fail.

#NAME? can appear if the function name is misspelled or if the formula has malformed text. In English-language Excel, the function name is WEEKDAY. Check that text arguments use straight double quotation marks and that parentheses are balanced. A formula copied from a formatted document may contain curly quotation marks that Excel does not accept as formula delimiters.

If the formula appears literally in the cell rather than calculating, the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula: select the cell, press F2, and press Enter. Simply changing the format may not cause an already-entered text string to calculate.

If the formula returns a number but the day seems wrong, check the return_type first. Also verify that the referenced cell is the intended date and that the date itself is correct. A cell can display only a weekday name or a shortened date, hiding a year or month that was entered incorrectly.

For formulas that update after source values change, check calculation mode. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. Pressing F9 recalculates open workbooks, but it is not a substitute for correcting an unintended Manual setting. If only some cells fail, look for inconsistent references, text values, or formulas overwritten with static values.

Excel’s formula argument separator can depend on regional settings. The examples here use commas, which are standard in many installations. If Excel rejects a correctly structured formula with a separator error, check the list-separator setting for your system; some installations require semicolons between arguments. Do not replace commas blindly if the formula already works in your environment.

Separate weekdays from working days

WEEKDAY identifies the day of the week; it does not account for weekends, company schedules, or holidays. A Saturday is still a weekday number, and a Monday holiday is still Monday. This difference matters when the actual question is “Is this date a workday?” rather than “Which day of the week is this?”

For a basic Monday-to-Friday check, with a date in A2, enter =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). For Saturday, January 20, 2024, this returns Weekend. It treats every Monday through Friday as a weekday, including public holidays. If a workplace has different weekend days or holiday rules, this simple test will not reflect them.

For workday counts or date calculations that exclude weekends and specified holidays, Excel provides functions such as NETWORKDAYS and WORKDAY. Those answer different questions from WEEKDAY: NETWORKDAYS counts working days between dates, while WORKDAY calculates a date a stated number of working days away. Check their weekend and holiday arguments against the schedule you are modeling; do not use a weekday number as a substitute for a complete business calendar.

A quick troubleshooting sequence

Work through these checks in order before rebuilding a workbook or changing a large range of formulas:

Click the source date cell and verify that the displayed date, month, and year are correct.
Test whether Excel recognizes it as numeric. If not, fix or convert the imported text carefully.
Confirm the formula points to the right cell and includes the numbering convention you expect.
Decide whether you need a weekday number, a written label, or a workday/holiday result; each requires a different approach.
If the formula is visible as text or has not updated, check the cell format and calculation mode.
Test the corrected method on a known date, such as January 15, 2024, which was a Monday, before filling it down.

Testing a known date is useful because it separates formula logic from questionable source data. If a Monday-first formula returns 1 for that known date but produces errors for imported rows, the numbering choice is probably sound and the problematic rows deserve closer inspection.

Frequently Asked Questions

Why does WEEKDAY return 2 for Monday?
Because the optional return_type is omitted or set to 1, which numbers Sunday as 1 and Monday as 2. Set return_type to 2 or 21 for Monday as 1 and Sunday as 7.

Why does WEEKDAY give me #VALUE!?
The date argument may be text, blank in a context that cannot be evaluated as a date, or otherwise invalid. Check the source cell and its regional date format, then convert text only after confirming its intended month/day order.

How do I show the weekday name?
Use TEXT with a real date and a weekday format such as dddd for a full name or ddd for an abbreviation. Alternatively, apply a custom dddd number format to the date cell if you want it to remain a date value.

Does WEEKDAY know about holidays?
No. It reports the day of the week only. A date that is a public holiday still receives its normal weekday number; holiday-aware workday calculations need a separate holiday list and a suitable workday function.

Why is the formula showing instead of the answer?
The cell may be set to Text, may contain a leading apostrophe, or Excel may be displaying formulas. Change the cell to General and re-enter it. If formula display is enabled for the sheet, toggle it off from the Formulas tab or use Ctrl+` on Windows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.