Excel WEEKDAY Function Not Working? Fix Common Causes

If Excel’s WEEKDAY function is returning an error or an unexpected number, first confirm the cell contains a real date and that you chose the intended return type. With a valid date in A2 and Monday-based numbering, enter =WEEKDAY(A2,2); Monday returns 1 and Sunday returns 7.

What the WEEKDAY function does

WEEKDAY returns a number representing the day of the week for a date. It does not return the weekday’s name, calculate a week number, or remove weekends from a schedule. Its basic syntax is WEEKDAY(serial_number,[return_type]): the first argument is the date, and the optional second argument controls how the days are numbered.

Excel stores recognized dates as serial numbers, with the displayed date usually controlled by cell formatting. That distinction matters: a cell can look like a date but contain text, or it can contain a valid date while displaying only its serial number. WEEKDAY needs a date value it can interpret, not merely characters that resemble a date.

For a concrete check, suppose A2 contains the actual date June 3, 2024, which was a Monday. With A2 holding that date, =WEEKDAY(A2,2) returns 1. The same date with the default numbering returns 2, because that system counts Sunday as day 1.

Check the return type before changing the date

A surprising result is often a numbering convention rather than a calculation failure. If you omit the second argument, Excel uses return type 1. Common choices 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 often convenient for work schedules.
3: Monday is 0, Tuesday is 1, and Sunday is 6.
11 through 17: Monday-through-Sunday numbering, with the selected day as 1. For example, 12 starts the week on Tuesday and 17 starts it on Sunday.

With June 9, 2024, a Sunday, in A2, =WEEKDAY(A2,1) returns 1, while =WEEKDAY(A2,2) returns 7. These are both correct. Decide which numbering convention your report, formula, or downstream calculation expects before treating one as wrong.

Return types outside the supported choices can produce #NUM!. For example, with a valid date in A2, =WEEKDAY(A2,0) returns #NUM! because zero is not a supported return type. Make sure a cell reference or a formula supplying the second argument is not accidentally producing an unsupported number.

Make sure the cell contains a real date

Dates imported from a website, CSV file, database, or another workbook may be stored as text. Text can look exactly like a date but fail when WEEKDAY tries to interpret it. A formula that returns #VALUE! is a clue, though the precise result can depend on the text and Excel’s regional date settings.

To check the underlying value, select the cell and look at the formula bar, then temporarily apply a number format such as General. A recognized date typically displays as a number when formatted as General. You can use Home > Number Format to switch formats; changing the display format alone does not convert text into a real date.

If A2 contains text in the exact year-month-day pattern 2024-06-03, the following formula builds a date from its components and asks for Monday-based numbering. With that text in A2, it returns 1 because June 3, 2024 was Monday:

=WEEKDAY(DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))),2)

This method assumes every value has the same four-digit-year, two-digit-month, two-digit-day layout. It is not a universal text-date converter; do not use it unchanged for values such as 6/3/2024 or dates containing time zones. For text in a regional format, first establish whether the text means June 3 or March 6, then convert it using a method that matches the source format. DATEVALUE can interpret many date strings, but its interpretation can depend on the computer’s regional settings.

If the source data is under your control, importing dates in a consistent, unambiguous format and converting them once is generally safer than embedding text-parsing logic in every WEEKDAY formula. Also check for leading apostrophes or spaces in imported values; either can prevent a date from being recognized as expected.

Handle blank cells and invalid inputs

A blank input is a common source of confusing results in a column of formulas. A formula that refers to a blank cell may treat the reference like zero, which is not a usable modern date serial for WEEKDAY and can result in an error. It is usually clearer to leave the result blank until a date is entered.

With A2 intended to contain a date but currently empty, use this formula to return an empty string instead of calculating WEEKDAY. When A2 contains a date, the formula returns its Monday-based weekday number:

=IF(A2="","",WEEKDAY(A2,2))

This guard is useful for genuinely empty cells and cells whose formula returns an empty string. It does not make invalid text into a date, so a nonblank value such as next Friday can still cause an error. If users can type arbitrary content, validate the input or handle errors deliberately rather than assuming that every nonblank cell is a date.

Excel’s valid date range and workbook date system also matter when values are imported or calculated. Values outside Excel’s supported date range are not valid dates for this calculation. Workbooks can use the 1900 or 1904 date system, and serial values are offset between them; this is especially relevant when copying raw date serial numbers between workbooks. Prefer transferring actual dates or clearly formatted source data instead of assuming a serial has the same meaning in every workbook.

Separate a wrong result from a display problem

WEEKDAY returns a number. If you expected “Monday” but see 1 or 2, the function may be working correctly and simply returning the numeric result you requested. To display the weekday name, format the original date cell with a day-name format, or use a separate text formula.

With a real date in A2, =TEXT(A2,"dddd") returns the full weekday name, such as Monday for June 3, 2024. The format code ddd gives an abbreviated name such as Mon. TEXT produces text, however, not a weekday number; keep WEEKDAY when later formulas need a numeric day value. The displayed language can also follow Excel or system language settings.

If a formula appears literally in the cell instead of showing a result, check whether the cell is formatted as Text. Change it to General, then re-enter the formula so Excel evaluates it. Also check for a leading apostrophe. If many formula cells show formulas rather than results, Excel’s Show Formulas setting may be on; switch it off from the Formulas tab. These are worksheet display or entry issues, not defects in WEEKDAY itself.

Know when WEEKDAY is the wrong function

Several date questions sound similar but require different calculations. WEEKDAY answers “which day of the week is this date?” It does not answer “which week of the year?” For a week number, use the relevant week-number function and confirm whether your organization follows a particular week-start or ISO week convention. A Monday-based WEEKDAY result does not, by itself, make a week number conform to ISO rules.

WEEKDAY also does not count business days or skip holidays. For schedules that need working-day dates, Excel has WORKDAY and WORKDAY.INTL; for counting working days between two dates, it has NETWORKDAYS and NETWORKDAYS.INTL. Those functions use their own weekend and holiday arguments. Replacing one of them with WEEKDAY can silently give an answer that is numerically valid but wrong for the business question.

A related misconception is that return type 2 makes WEEKDAY ignore Saturday and Sunday. It only changes the numbering. For instance, with a Sunday in A2, =WEEKDAY(A2,2) returns 7; it has not excluded that date from a workday calculation. Use a business-day function when weekends or holidays need to be excluded.

A practical troubleshooting sequence

When the output still seems wrong, isolate the cause instead of rewriting the whole formula. Check these items in order:

Confirm the expected result: do you want a number, a weekday name, a week number, or a business-day calculation?
Inspect the date cell’s underlying value. Confirm it is a genuine Excel date, not text that only looks like one.
Check the second argument. Decide whether Sunday or Monday should be numbered 1, then use a supported return type explicitly.
Look for blank cells, spaces, apostrophes, error values, or dates outside the supported range in the referenced cell.
Check whether the formula is being evaluated. A Text-formatted cell or a leading apostrophe can make formula text appear unchanged.
Verify the displayed result against a known calendar date. This separates a numbering-convention difference from an actual input problem.

On Windows, select the formula cell and inspect the formula bar; you can also use Formulas > Evaluate Formula to step through a calculation. The Mac ribbon offers the corresponding formula-auditing tools, although exact placement can vary slightly by Excel release. Excel for the web supports WEEKDAY formulas too, but some ribbon controls and the interface layout differ from the Windows desktop app. Regardless of platform, the key checks remain the date value and return type.

Frequently Asked Questions

Why does WEEKDAY return a number instead of Monday?
WEEKDAY is designed to return a numeric day index. To show a name, format a date with a weekday display format or use TEXT with a format such as dddd. Use the numeric result when another formula needs to test or compare weekdays.

Why does WEEKDAY return 2 for a Monday?
That is the expected result when the return type is omitted or set to 1, because Sunday is numbered 1 in that system. Set the second argument to 2 if Monday should be 1; the same Monday then returns 1.

Why am I getting #VALUE! from WEEKDAY?
The referenced value may be text that Excel cannot interpret as a date, or it may contain unexpected characters. Check the source format, regional interpretation, and whether the cell is truly a date serial. A valid-looking display does not prove that the underlying value is a date.

Why does WEEKDAY return #NUM!?
Common causes include an unsupported return type or an invalid date serial outside Excel’s acceptable date range. Check the second argument first, then inspect the referenced date and any formula that produces it.

Can WEEKDAY tell me whether a date is a weekend?
It can provide the number you need to test, but it does not label or exclude weekends automatically. Choose a return type that makes your logic clear, then compare the result with the Saturday and Sunday values for that numbering system. For workday scheduling with holidays, use a function intended for business days.

Does changing the cell format fix a text date?
No. Number formatting changes how a recognized value is displayed; it does not reliably convert text into a date value. Convert the source text into a real date first, then apply the display format you want.

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.