Excel’s WEEKDAY function returns a number for the day of the week represented by a date. If cell A2 contains a real Excel date, enter =WEEKDAY(A2,2) to number Monday as 1 through Sunday as 7; for a Saturday date, the result is 6.
What the WEEKDAY function returns
WEEKDAY converts a date into a numeric weekday code. It does not change the date or display a weekday name by itself. The number you get depends on the function’s optional return_type argument, which sets which day starts the week and what number it receives.
This is useful when a spreadsheet needs to test or calculate with weekdays. For example, you can identify weekend dates, group records by weekday, or build a rule that treats Saturdays and Sundays differently. A date’s visible format does not determine the result: a cell can display “Saturday,” “15-Jun-2024,” or “6/15/2024” while still representing the same underlying date.
The function is available in Microsoft Excel for Microsoft 365 on Windows, as well as other modern Excel editions. Its basic behavior is the same in Excel for Mac and Excel for the web. The examples here use commas between arguments, as in standard English-language Excel formula syntax; some regional settings use semicolons instead.
Syntax and return types
The function syntax is WEEKDAY(serial_number, [return_type]). The serial_number is the date to evaluate. The optional return_type determines the numbering scheme. If you leave it out, Excel uses type 1, so Sunday is 1 and Saturday is 7.
- Type 1: Sunday = 1, Monday = 2, through Saturday = 7. This is the default.
- Type 2: Monday = 1, Tuesday = 2, through Sunday = 7. This is often convenient for Monday-to-Sunday workweeks.
- Type 3: Monday = 0, Tuesday = 1, through Sunday = 6.
- Types 11–17: Each sets a different first day of the week and numbers that day as 1. Type 11 starts Monday, 12 Tuesday, 13 Wednesday, 14 Thursday, 15 Friday, 16 Saturday, and 17 Sunday.
Types 2 and 11 both start the week on Monday and number Monday as 1; type 2 ends Sunday at 7, while type 11 also numbers the days Monday through Sunday from 1 to 7. The less commonly used types 11–17 can be helpful when a reporting or scheduling convention starts on a day other than Sunday or Monday. Excel does not accept every integer as a return type: an unsupported value produces the #NUM! error.
How to enter a basic WEEKDAY formula
First make sure the input cell contains a valid Excel date, not merely text that looks like one. For a date of June 15, 2024, stored in A2, the formula below returns 6 with type 2 because that date was a Saturday.
With the date June 15, 2024, in A2, enter: =WEEKDAY(A2,2). The result is 6.
To see the default numbering instead, use the same date in A2 and omit return_type: =WEEKDAY(A2). The result is 7, because the default scheme assigns Saturday the number 7. This contrast is why it is good practice to include the return_type explicitly in formulas: the chosen numbering convention is visible and less likely to be misunderstood later.
To apply the calculation to more dates, enter the formula next to the first date and fill it down. In Excel for Microsoft 365 on Windows, you can drag the fill handle at the lower-right corner of the formula cell, or copy and paste the formula into the rows below. Excel adjusts A2 to A3, A4, and so on as the formula is copied. The same approach works in Excel for Mac and Excel for the web.
Choosing the right numbering scheme
Choose the numbering scheme that matches the task, rather than assuming that one is universally correct. For a typical Monday-to-Friday business schedule, type 2 is practical because weekdays have values 1 through 5 and weekend days have values 6 and 7. For a calendar that treats Sunday as the first day, the default type 1 may be more familiar.
Type 3 starts Monday at zero, which some calculations may require, but it can be less intuitive for people reading a report. Types 11–17 make it possible to set another starting day, but remember that selecting a different first day changes the codes assigned to every day. If one worksheet uses type 1 and another uses type 2, the same numeric result can mean different weekdays. Label the numbering convention in a column heading, note, or formula documentation when sharing a workbook.
A useful check is to test the formula against a date whose weekday you know. If the result does not match, confirm both the date value and the return_type before building further calculations on it.
Flagging weekends and other weekday rules
With Monday numbered 1 and Sunday numbered 7, values greater than 5 identify Saturday and Sunday. You can use that test to label each row. If A2 contains the date to classify, this formula returns “Weekend” for a Saturday or Sunday and “Weekday” for Monday through Friday:
With a date in A2, enter: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").
For the June 15, 2024 example, the result is Weekend. The comparison works because the formula explicitly uses type 2. If you remove the second argument, Sunday is still greater than 5, but other day-number tests can become confusing when the numbering system changes. Keeping the return type consistent makes rules easier to audit.
You can also build a numeric test, for example to identify weekdays only. With a date in A2, =WEEKDAY(A2,2)<6 returns TRUE for Monday through Friday and FALSE for Saturday or Sunday. A TRUE/FALSE result can be used in other formulas, filters, or logical tests. If your organization has a different weekend, such as Friday and Saturday, the standard weekend check needs to be adapted to match those days rather than assuming Saturday and Sunday are always nonworking days.
Displaying a weekday name instead of a number
If you want a visible weekday name, WEEKDAY is not the only option. You can format a date cell to show a name while keeping its underlying date value, or use the TEXT function to return a text label. With the date in A2, =TEXT(A2,"dddd") returns the full weekday name, such as “Saturday”; =TEXT(A2,"ddd") returns an abbreviated name, such as “Sat.” The result is text, not a weekday number.
That distinction matters in later calculations. WEEKDAY is useful when a formula must compare, sort, or test weekday codes. TEXT is useful when a report needs a readable label. If you only need to change how a date appears in its cell, select the date cell and use Home > Number Format to choose a date format, or open Format Cells and set a custom format such as dddd. Formatting keeps the value as a date, whereas a TEXT formula produces a text result. Names and abbreviations can also reflect the language and regional settings of Excel.
Common errors and date issues
A frequent problem is that a date-looking entry is stored as text. WEEKDAY may return #VALUE! when it cannot interpret the supplied value as a valid date. Text dates can also be ambiguous: a string such as 03/04/2024 may mean March 4 or April 3 depending on regional settings. When a result looks wrong, select the input cell and check whether Excel recognizes it as a date; changing the cell’s display format alone does not necessarily convert text into a date.
#VALUE!: Check whether the input is a valid date or a number Excel can interpret as a date. Re-entering the value in a clear, region-appropriate date format may help.#NUM!: Check the serial number and return_type. The return_type must be one of the supported values: 1, 2, 3, or 11 through 17.- An unexpected weekday number: Confirm whether the formula omits return_type or uses a different numbering system than the one you expect.
- A date that appears correct but calculates incorrectly: Check for text values, hidden time portions, or a different regional interpretation of the date.
Excel stores recognized dates as numbers, which is why date arithmetic and weekday calculations work. A date cell may include a time as well; WEEKDAY uses the date portion to determine the weekday, so a time such as 2:30 p.m. does not turn Saturday into Sunday. When importing dates from another system, inspect a few source values before relying on a large set of results.
WEEKDAY compared with related approaches
WEEKDAY answers a different question from a date format. Formatting changes the displayed appearance of a date, while WEEKDAY returns a number that formulas can evaluate. TEXT can display a weekday name, but its output is text. Choosing among them depends on whether the next step is calculation, presentation, or both.
For example, a schedule might keep the original date in column A, use WEEKDAY in column B to identify business days, and show a formatted date or weekday label in a report. This separation makes it easier to preserve the actual date for sorting and calculations while still giving readers a clear label. Avoid replacing dates with weekday names if later steps need the full date, because many different dates share the same weekday name.
Frequently Asked Questions
What is the WEEKDAY formula in Excel?
Use =WEEKDAY(date,return_type), replacing date with a cell reference or valid date value. The return_type is optional, but including it makes the weekday numbering clear.
How do I make Monday equal 1 in Excel?
Use return_type 2 or 11. For a date in A2, =WEEKDAY(A2,2) returns 1 for Monday and 7 for Sunday.
How do I check whether a date is a weekend?
For a date in A2 and a Saturday–Sunday weekend, test WEEKDAY(A2,2)>5. It is TRUE for Saturday and Sunday and FALSE for Monday through Friday.
Why does WEEKDAY return a number instead of a day name?
That is the function’s purpose: it returns a weekday code for calculations. Use a date format or the TEXT function with a format such as dddd when you need a displayed name.
Can WEEKDAY work with a date typed directly in the formula?
Yes, but date literals can be interpreted differently by regional settings. A cell containing a recognized Excel date is usually clearer and easier to maintain than a hard-coded date string.
Related
- Excel Not Equal: The <> Operator Explained
- Excel Arrow Keys Not Moving Between Cells? Quick Fixes
- Excel Not Responding? Causes, Fixes, and Recovery Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Conditional Formatting If Statements in Excel: A GuideIn Excel, an “if statement” for conditional formatting is usually a formula-based rule that returns TRUE or FALSE;…
- Excel Formula Showing Instead of Result? Causes and FixesIf Excel is showing a formula instead of its result, first check whether Show Formulas is turned on;…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
- Excel IFS Function: Syntax, Examples, and AlternativesIn Microsoft Excel, the IFS function checks multiple conditions in order and returns the result paired with the…
- How to Add a Formula in an Excel ColumnTo add a formula to an Excel column, enter it in the first row that needs a result,…
- Round Excel to Significant Figures: Formulas and MethodsTo round a number in Excel to a chosen number of significant figures, put the value in A2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.