Excel WEEKDAY: Get the Day of the Week from a Date

In Excel, use WEEKDAY to return a number for the day of the week represented by a date. If A2 contains March 5, 2025, =WEEKDAY(A2) returns 4, because Excel’s default numbering starts with Sunday as 1 and Wednesday as 4.

What WEEKDAY Returns

WEEKDAY evaluates a date and returns an integer from 1 through 7. The result is a day index, not the written name of the day. Excel stores genuine dates as serial numbers, so the function can calculate the weekday from the underlying date value rather than from how the cell looks on the sheet.

With the date March 5, 2025, in A2, =WEEKDAY(A2) returns 4. Changing A2’s number format to display the date as “5-Mar-25” or “Wednesday, March 5” does not change that result: the cell still represents the same date. The optional second argument, called return_type, controls which day receives the number 1.

The basic syntax is WEEKDAY(serial_number, [return_type]). The date is required; return_type is optional. If you leave it out, Excel uses the Sunday-first numbering system. The function is useful for calculations and sorting, while a text function or a custom format is better when you want a readable weekday label.

Choose Which Day Starts the Week

Pick the numbering system that matches your report or calculation. The two most commonly useful choices are:

Return_type 1, or omitted: Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7.
Return_type 2: Monday = 1 through Sunday = 7. This is often convenient when the workweek starts on Monday.
Return_type 3: Monday = 0 through Sunday = 6. This can be useful when calculations need Monday to have a zero-based index.

With March 5, 2025, in A2, =WEEKDAY(A2,2) returns 3: Monday is 1, Tuesday is 2, and Wednesday is 3. With the same date in A2, =WEEKDAY(A2,3) returns 2, because this system assigns Monday the value 0. The date itself has not changed; only the numbering convention has.

Excel also supports return_type values 11 through 17 for systems that begin the week on a specified day. For example, 11 begins with Monday, 12 with Tuesday, and 17 with Sunday. These options still return values from 1 to 7. For most workbooks, using 1 or 2 makes formulas easier for other people to recognize.

Display the Weekday Name

If the goal is to show “Wednesday” rather than the number 4, format the date cell instead of replacing the date with a calculated result. Select the date cells, press Ctrl+1 on Windows, choose the Number tab, select Custom, and enter dddd as the format. Use ddd for an abbreviated label such as “Wed.” This changes the display, not the underlying date, so the cell remains usable in date calculations.

On a Mac, Command+1 opens Format Cells. The menu layout can vary slightly by Excel release, but the Custom number format works the same way. Excel for the web also supports date formatting through the number-format controls, though available dialog options may differ from the desktop app.

To put a weekday name in a separate cell, with the date March 5, 2025, in A2, enter =TEXT(A2,"dddd") in B2. The result is “Wednesday.” For a short name, with the same layout, use =TEXT(A2,"ddd"); it returns “Wed.” The result of TEXT is text, not a date, so it is less suitable than a formatted date cell when later formulas need to calculate with the original date.

The name produced by TEXT can depend on Excel’s language and regional settings. A workbook opened in another locale may show a localized day name or interpret date text differently. WEEKDAY’s numeric result is usually the steadier choice for logic; use TEXT when a displayed label is the priority.

Use WEEKDAY to Identify Weekends

A common task is to mark Saturday and Sunday. With a date in A2, use Monday-first numbering so the two weekend days are 6 and 7. For example, if A2 contains March 8, 2025, the formula =WEEKDAY(A2,2)>5 returns TRUE, because that date is a Saturday and its Monday-first weekday number is 6. For a Monday date, the comparison returns FALSE.

To show a readable label instead of TRUE or FALSE, with the date still in A2, enter =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). For March 8, 2025, it returns “Weekend”; for March 5, 2025, it returns “Weekday.” The second label means Monday through Friday in this particular test. It does not account for public holidays, company closures, or a work schedule that uses different days off.

You can fill this formula down next to a list of dates and then filter the helper column. If you are using an Excel table, entering the formula in one row of a calculated column generally fills it through the column. Check that the dates are genuine Excel dates and that the formula references the date column in the same row.

Weekdays, Workdays, and Holidays Are Different

“Day of the week” and “working day” are related but not interchangeable. WEEKDAY identifies a day in the repeating seven-day calendar cycle. A Monday is still Monday even if it is a holiday. A weekend check based on WEEKDAY only treats Saturday and Sunday as nonworking days; it does not know your organization’s schedule.

For workday counts or date deadlines, Excel also has NETWORKDAYS and WORKDAY. NETWORKDAYS counts working days between two dates, and WORKDAY calculates a date a specified number of working days away. Both can accept a holiday range, and versions of these functions also provide options for nonstandard weekend patterns. These are more appropriate than WEEKDAY when the question is “How many business days?” or “What date is five workdays later?”

For example, a Monday-to-Friday schedule that excludes listed holidays requires more information than a weekday number: it needs the start and end dates, and the holiday dates if those should be skipped. WEEKDAY remains useful alongside those functions—for example, to label a date or inspect a specific day—but it does not itself calculate a holiday-aware workday count.

Check That Excel Recognizes the Date

Many apparent WEEKDAY errors begin with the input rather than the function. A date may look right on screen but be stored as text. This can happen after copying data from a website, importing a CSV file, or entering dates in a format Excel does not recognize under the current regional settings. If the function returns an error or an unexpected result, select the input cell and check whether Excel treats it as a date value.

Try changing the cell’s number format to a date format. If it remains unchanged text, the value may not be a genuine date.
Check for leading apostrophes, extra spaces, or inconsistent date formats in imported data.
Confirm the month-and-day order. Text such as 04/05/2025 can mean different dates in different regional settings.
Use a consistent date column and convert imported text dates before relying on weekday calculations.

Another common surprise is receiving a number when you expected a name. That is normal: WEEKDAY returns a numeric index. Use a date number format or TEXT for a label, and use the numeric result when writing comparisons or sorting logic. Also verify the return_type: a Wednesday is 4 with the default system, 3 with return_type 2, and 2 with return_type 3.

Excel’s historical date system includes a compatibility treatment of the year 1900, so calculations involving very old dates can have special behavior. For ordinary modern schedules, invoices, and calendars, the practical issue is more often text-versus-date recognition or a mismatched weekday numbering system.

Choose the Right Method for Your Sheet

There is no single best display for every workbook. Choose the method based on whether you need a calculation, a visible label, or a calendar-like presentation:

Use WEEKDAY when another formula needs a dependable number, such as testing whether a date falls on Saturday or Sunday.
Format the date as ddd or dddd when readers should see the weekday but the cell must remain a real date.
Use TEXT when you need the weekday name as text in a separate result cell, such as in a report heading or label.
Use NETWORKDAYS or WORKDAY when the real question concerns business-day counts or deadlines, particularly when holidays matter.

For a fixed English label independent of the workbook’s display language, a CHOOSE formula can map Monday-first numbers to names. With the date March 5, 2025, in A2, =CHOOSE(WEEKDAY(A2,2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday") returns “Wednesday.” This is explicit, but it is longer to maintain than a date format, and the labels are fixed in the language written into the formula.

Sorting deserves attention too. Sorting by weekday name alphabetically puts Friday before Monday, which is not calendar order. Sorting by a WEEKDAY result using a consistent return_type gives the intended sequence. If the source dates must remain chronological, sort by the dates themselves; a weekday number groups recurring days but does not preserve the year-and-month sequence.

Frequently Asked Questions

What number is Monday in Excel WEEKDAY?

It depends on return_type. With the default, Monday is 2. With WEEKDAY(date,2), Monday is 1; with WEEKDAY(date,3), Monday is 0. Set the return_type explicitly when the numbering matters.

How do I get the day name instead of a number?

Format the original date with dddd for the full name or ddd for an abbreviation. Alternatively, use TEXT in another cell; for a date in A2, =TEXT(A2,"dddd") returns the full weekday name.

Why does WEEKDAY return the wrong day?

Check that the input is a real date, not text, and confirm that the cell contains the intended month and day. Then check the return_type: changing it changes the number assigned to each weekday, even though the underlying weekday is unchanged.

How do I make Monday the first day of the week?

Use return_type 2, as in =WEEKDAY(A2,2). This numbers Monday as 1 and Sunday as 7, which is convenient for many Monday-to-Friday work schedules.

Can WEEKDAY tell whether a date is a workday?

It can check a simple Monday-to-Friday pattern, but it does not know about holidays or custom schedules. Use NETWORKDAYS or WORKDAY when holiday-aware workday calculations are needed, and provide the holiday dates where appropriate.

Practical Takeaway

For most weekday calculations, start with WEEKDAY(date,2) when Monday should be 1, or omit the second argument when you want Excel’s default Sunday-first numbering. Use a date format to display a weekday without losing the underlying date, and use a workday function when weekends and holidays must affect a deadline or count. Keeping those three jobs—numbering, display, and business-day calculation—separate makes formulas easier to understand and reduces common mistakes.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.