Excel Weekday Formula Not Working? Causes and Fixes

If an Excel weekday formula is not working, first check that the cell contains a real Excel date, then use =WEEKDAY(A2,2) to return Monday as 1 through Sunday as 7. If the result is a number when you expect a name, or the number seems wrong, the issue is usually the return-type argument, text-formatted dates, or cell formatting—not a broken function.

How WEEKDAY works

Excel stores dates as serial numbers and calculates a weekday from that date value. The WEEKDAY function returns a number, not a weekday name. Its syntax is WEEKDAY(serial_number,[return_type]): the first argument is the date or a cell containing it, and the optional second argument determines how the days are numbered. If you omit the second argument, Excel uses type 1.

With the date 2 November 2026 in A2, the formula =WEEKDAY(A2,2) returns 1 because that date is a Monday. With the same date in A2, =WEEKDAY(A2,1) returns 2 because type 1 numbers Sunday as 1 and Monday as 2. These are different numbering conventions, not conflicting calculations.

Type 1 or omitted: Sunday = 1 through Saturday = 7.
Type 2: Monday = 1 through Sunday = 7.
Type 3: Monday = 0 through Sunday = 6.

WEEKDAY also supports other return types for different workweek patterns. Choose the type that matches the numbering your report, schedule, or formula needs. If another formula assumes Monday is 1 but WEEKDAY is using its default Sunday-first system, its comparisons may be off by one even though WEEKDAY is functioning correctly.

Check whether the date is a real Excel date

A value that looks like a date may actually be text. This can happen after copying data from a website, importing a CSV, or receiving dates in a format that does not match your computer’s regional settings. WEEKDAY needs a valid date value; text that Excel cannot interpret as a date can produce #VALUE! or an incorrect result after conversion.

Click the date cell and inspect the formula bar. If the value is left-aligned, has a leading apostrophe, or does not respond to date formatting, it may be text. Alignment alone is not conclusive because cells can be formatted manually. A practical check is to temporarily choose Home > Number Format > General. A true date will normally display as a serial number; text will remain text. Restore the preferred date format afterward.

For a text date in A2 that Excel can interpret under the current regional settings, use =DATEVALUE(A2) in B2 to convert it to a date serial. For example, if A2 contains the text 11/2/2026 and Excel reads that as November 2, 2026, B2 contains that date as a numeric value. The interpretation of text such as 11/2/2026 is ambiguous: it can mean November 2 or February 11, depending on regional conventions. Confirm the source format before converting a column, and check a few results rather than assuming the interpretation is correct.

If the text dates use an unambiguous format that Excel does not recognize, use Data > Text to Columns to specify the column’s date order during conversion, or correct the source data. Do not convert ambiguous dates in bulk without verifying whether the source uses month/day/year or day/month/year.

Fix a #VALUE! error

#VALUE! commonly means the first argument is not a usable date or number. Check that the formula points to the intended cell, that the cell is not blank or text containing extra characters, and that imported values have been converted. A date with a leading or trailing space, for example, may not be parsed the way you expect. Also inspect the formula for a misspelled function name or unmatched parentheses.

If A2 may contain a valid date or a blank, and you want B2 to stay blank when A2 is blank, enter =IF(A2="","",WEEKDAY(A2,2)) in B2. This avoids displaying a weekday result for an empty input. It does not repair text dates: convert those first. If the cell contains a non-date text value, the formula can still return an error.

When converting a column, keep the original data until you have confirmed the result. If you use a helper column with DATEVALUE, verify several dates near the beginning and end of the range, as well as dates where the day is 12 or less. Those are especially useful for detecting a month/day reversal.

Understand numbers, names, and cell formatting

A correct WEEKDAY formula returns a number. To display a weekday name, use Excel’s TEXT function instead. With a real date in A2, =TEXT(A2,"dddd") returns the full weekday name, such as Monday; =TEXT(A2,"ddd") returns an abbreviated name, such as Mon. The result is text, which matters if you later sort or compare it.

Another option is to keep the numeric result and format the date cell itself. A custom number format such as dddd can display a date as a weekday name while leaving the underlying value as a date. This approach is useful when you want to preserve date calculations. However, it does not turn a WEEKDAY result into a date: applying a date format to the number 1 will display a date-like value based on Excel’s date system, not the word Monday. Apply weekday formats to actual date cells, not to the numeric output of WEEKDAY.

Be clear about whether downstream work needs a number, a date, or text. Use WEEKDAY for numeric logic, such as identifying weekends. Use TEXT when a readable label is needed. Keep the original date when you still need sorting, date arithmetic, or filtering by date.

Check the return type and the formula reference

A weekday that is consistently one day earlier or later often points to a return-type mismatch. Decide whether your week starts on Sunday or Monday, and include the return type explicitly rather than relying on the default. For a Monday-first numbering system, use type 2; this makes weekend checks and weekday labels easier to reason about.

For example, with a date in A2, =WEEKDAY(A2,2)>5 returns TRUE for Saturday and Sunday and FALSE for Monday through Friday. On a Monday, WEEKDAY returns 1, so the comparison 1>5 is FALSE; on Sunday it returns 7, so 7>5 is TRUE. If your workbook treats a different set of days as the weekend, this test needs a different rule.

When copying a formula down, confirm that each row references its own date. In B2, =WEEKDAY(A2,2) should normally become =WEEKDAY(A3,2) when filled into B3. A reference with dollar signs, such as $A$2, is fixed and will keep using A2. Fixed references are useful in some calculations, but are a frequent cause of repeated weekday results in a copied column.

Resolve formula separators and calculation delays

Excel’s formula argument separator can depend on regional settings. The examples here use commas. If Excel rejects a formula with a syntax warning, your installation may expect semicolons instead; enter the same arguments using the separator Excel accepts. This is a regional formula-entry difference, not a different version of WEEKDAY. Function names may also be localized in some language editions.

If the formula is accepted but the result does not change after you edit a date, check calculation mode. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. If the workbook is set to Manual, press F9 to recalculate formulas; Shift+F9 recalculates the active worksheet. On Mac, use the Formulas tab’s calculation options where available, or check Excel > Preferences > Calculation in versions that provide that path. Excel for the web generally recalculates supported formulas automatically.

Also check whether the cell is displaying the formula as text instead of calculating it. If you see the literal characters =WEEKDAY(A2,2), the cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Change the cell format to General, then edit and re-enter the formula. Merely changing the format may not convert a formula already stored as text.

Use a reliable troubleshooting sequence

Work through these checks in order; each isolates a different cause without changing the source data unnecessarily:

Click the formula cell and confirm the formula begins with an equals sign and points to the correct date cell.
Check that the input is a real date, not text, a blank, or an unexpected value.
Add the return type explicitly, such as 2 for Monday-first numbering.
Decide whether the desired output is a number, a name, or a formatted date, and use the appropriate method.
If the formula is rejected, check the argument separator and regional date interpretation.
If the result is stale or appears as text, check calculation mode and the formula cell’s number format.

For a small test, enter a known date into an empty cell and calculate its weekday separately. With the date November 2, 2026 in A2, =WEEKDAY(A2,2) should return 1. If the test works but your original row does not, the problem is likely that row’s input or reference rather than the function. If the test fails too, inspect the formula syntax, separator, and calculation settings.

Frequently Asked Questions

Why does WEEKDAY return a number instead of Monday or Tuesday?
That is its normal behavior: WEEKDAY returns a number based on the selected return type. To display the name, use TEXT with a real date, for example =TEXT(A2,"dddd"), or format the original date cell with a weekday date format.

Why is my Excel weekday one day off?
The formula may be using a different week-numbering convention from the one you expect. WEEKDAY’s omitted return type starts the week on Sunday. Use =WEEKDAY(A2,2) when you want Monday = 1 and Sunday = 7, and confirm that later comparisons use the same convention.

Why does WEEKDAY show #VALUE!?
The referenced value may be text Excel cannot parse as a date, or the formula may point to a cell with unexpected contents. Check the source, resolve any date-format ambiguity, and convert valid text dates before using WEEKDAY.

How do I make Excel show the weekday name but keep the date?
Apply a weekday number format such as dddd to the cell containing the real date. The displayed value becomes a name, while the stored value remains a date that can still be used in date calculations.

How can I identify Saturday and Sunday?
With a date in A2 and Monday-first numbering, =WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday. If your workweek uses different weekend days, adjust the rule rather than assuming Saturday and Sunday are always the only non-workdays.

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.