To calculate a weekday from a date in Excel, use =WEEKDAY(A2,2) if the date is in A2; Monday returns 1 and Sunday returns 7. To show the weekday’s name instead, use =TEXT(A2,"dddd"), which returns a name such as Monday.
Choose a weekday number or name
The right formula depends on what you want to do with the result. A weekday number is useful for calculations, sorting, rules, or reports. A weekday name is easier to read in a schedule or list. Both formulas use the date stored in A2, so replace A2 with the cell that contains your date.
- Number, Monday as 1: With a valid date in A2,
=WEEKDAY(A2,2)returns a number from 1 to 7. For June 17, 2024, a Monday, it returns 1. - Number, Sunday as 1: With the same date in A2,
=WEEKDAY(A2,1)returns 2. This numbering is useful when your calendar treats Sunday as the first day of the week. - Full name: With June 17, 2024 in A2,
=TEXT(A2,"dddd")returns Monday. - Abbreviated name: With June 17, 2024 in A2,
=TEXT(A2,"ddd")returns Mon.
The second argument in WEEKDAY is called the return type. Choosing it deliberately prevents confusion: a result of 1 does not mean Monday unless you choose a return type that assigns Monday the value 1.
How WEEKDAY numbering works
WEEKDAY converts a date into an integer representing its position in a week. The date itself remains unchanged; the formula simply reports its weekday using the numbering convention you select. If you omit the second argument, Excel uses return type 1, where Sunday is 1, Monday is 2, and Saturday is 7.
For most Monday-to-Sunday work schedules, return type 2 is convenient: Monday is 1, Tuesday is 2, and Sunday is 7. Return type 3 also starts the week on Monday, but numbers Monday as 0 and Sunday as 6. Excel also accepts return types 11 through 17, which let you choose a different first day while numbering that day as 1. For example, 11 starts on Monday and 17 starts on Sunday.
With a date of June 19, 2024 in A2, the result is 3 for =WEEKDAY(A2,2), because that date is Wednesday. The same date produces 2 with =WEEKDAY(A2,3), because this version counts Monday as zero. These are both correct results; they use different numbering systems.
Display a weekday name with TEXT
TEXT converts a number or date into text that follows a format code. With June 17, 2024 in A2, =TEXT(A2,"dddd") returns Monday, while =TEXT(A2,"ddd") returns Mon. The result is text, not a date or a weekday number. That makes it suitable for labels, but less suitable for calculations that expect a number.
Names returned by TEXT follow the language and regional settings used by Excel. A workbook opened with different regional settings may display a localized weekday name. If you need a numeric weekday for logic that should not depend on display language, use WEEKDAY instead. Also note that TEXT’s result is a text value: sorting weekday names alphabetically will not put them in calendar order. Sort by a separate weekday-number column if order matters.
Enter the formula and fill it down
In Excel for Microsoft 365 on Windows, enter dates in one column and the weekday formula in the next. For example, if dates are in A2:A20, click B2, enter =WEEKDAY(A2,2), and press Enter. Select B2 again and use the fill handle—the small square at the lower-right corner of the cell—to copy the formula down. Excel adjusts A2 to A3, A4, and so on.
To create readable weekday labels instead, with dates in A2:A20, enter =TEXT(A2,"dddd") in B2 and fill down. You can also enter the formula in a cell and copy it with Ctrl+C, select the destination cells, and press Ctrl+V. On a Mac, use Command+C and Command+V for copying and pasting. The formulas themselves work in Excel for Windows, Mac, and the web; the ribbon layout can differ slightly between desktop and browser versions.
If you want to build a date directly in a formula rather than type it into a cell, with A2 intended to hold June 17, 2024, enter =DATE(2024,6,17) in A2. Excel returns a real date, which is a Monday. You can then refer to A2 with WEEKDAY or TEXT as above. DATE uses the year, month, and day in that order, avoiding ambiguity between regional date-entry styles.
Format a date cell to show the weekday
You do not have to create a separate text result to display a weekday. If you want a cell to remain a real date—for example, so it can still be sorted chronologically or used in date arithmetic—apply a custom number format. The underlying date remains intact while its display changes.
- Select the date cells you want to change.
- On the Home tab, open the Number Format drop-down and choose More Number Formats. In the Format Cells window, select Custom.
- Enter
ddddfor the full weekday name ordddfor an abbreviation, then confirm.
A cell containing June 17, 2024 will display Monday with the dddd format, but it still contains the original date. To display both date and weekday, use a custom format such as dddd, mmmm d, yyyy; the same cell will display Monday, June 17, 2024. Formatting changes appearance, not the stored value. If you need the weekday as a separate value for a formula or export, use WEEKDAY or TEXT in another cell.
Make sure Excel recognizes the date
Excel stores dates as serial values and displays them using date formats. A date that looks correct may instead be text, especially if it was imported from a website, a CSV file, or a system using a different date order. WEEKDAY needs a valid Excel date value; if its input is text Excel cannot interpret, the formula may return an error or an unexpected result.
To check a suspicious cell, select it and inspect the Number Format setting on the Home tab. You can also try changing its format to General: a recognized date typically displays as a number. That number is Excel’s internal date serial, not a weekday result. Do not fix a text-date problem merely by applying a date format; formatting text does not convert it into a date value.
When entering dates in formulas or building them from separate year, month, and day values, DATE is a reliable option. For example, with year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2) in D2. If A2 is 2024, B2 is 6, and C2 is 17, D2 becomes June 17, 2024; applying =WEEKDAY(D2,2) in E2 returns 1. If dates arrive as text, convert them using a method suited to the source and regional date order, then verify the converted value before relying on weekday results.
Weekday versus working-day calculations
A weekday calculation identifies which day of the week a date falls on. It does not determine whether that date is a working day for a particular company, country, or schedule. A Saturday can be a normal workday for one roster, while a weekday can be a holiday for another.
For a count of workdays between two dates, use NETWORKDAYS. With a start date in A2, an end date in B2, and holiday dates in H2:H10, enter =NETWORKDAYS(A2,B2,$H$2:$H$10). If A2 is Monday, June 17, 2024, B2 is Friday, June 21, 2024, and none of the listed holidays fall in that interval, the result is 5. NETWORKDAYS counts the starting and ending dates when they are workdays and excludes Saturday and Sunday by default; the holiday range is optional.
If your weekend is not Saturday and Sunday, Excel’s NETWORKDAYS.INTL function supports a custom weekend pattern. This is a separate question from finding the weekday name, so choose the function according to the task: use WEEKDAY to label or classify one date, and a workday function to count or calculate business days. For organization-specific schedules, confirm which holidays and weekend days should be excluded.
Common problems and how to fix them
- The number seems one day off: Check the return type.
=WEEKDAY(A2)uses Sunday as 1, while=WEEKDAY(A2,2)uses Monday as 1. The date may be fine; the numbering convention may differ from what you expected. - The formula shows #VALUE!: Check whether the referenced cell contains a recognized date rather than text, an empty string, or an unexpected value. Re-enter or convert the source date, and confirm that its year, month, and day are interpreted correctly.
- The result shows a date instead of a number: The formula cell may have a date number format. Change its format to General or Number to display the WEEKDAY result as an integer.
- The weekday name is in another language: TEXT follows regional and language settings. If the output must be language-independent for calculations, use WEEKDAY; if a particular display language is required, confirm the workbook’s expected locale.
- Excel rejects a formula with commas: Some regional settings use semicolons as formula argument separators. If Excel expects semicolons, replace the commas accordingly, such as
=WEEKDAY(A2;2). The function and its arguments are unchanged.
One further point: changing a cell’s display format does not change its date value or correct a misread date. If a source says 03/04/2024, it may mean March 4 or April 3 depending on its origin. Verify the intended date before calculating its weekday, especially when importing data from another region.
Frequently Asked Questions
What is the Excel formula for the day of the week?
Use =WEEKDAY(A2,2) to return a number from 1 for Monday through 7 for Sunday, assuming the date is in A2. Use =TEXT(A2,"dddd") if you want the weekday’s full name instead.
How do I get Monday instead of Sunday as day 1?
Add 2 as WEEKDAY’s second argument: =WEEKDAY(A2,2). This sets Monday to 1 and Sunday to 7. Without the second argument, Excel defaults to Sunday as 1.
Can Excel show the weekday without another formula?
Yes. Apply a custom number format of dddd to the date cell to show the full name, or ddd to show an abbreviation. The cell remains a real date, unlike a weekday name produced as text by TEXT.
Why does WEEKDAY return a number instead of Monday or Tuesday?
WEEKDAY is designed to return a numeric position in the week. To show a name, use TEXT with a weekday format code, such as =TEXT(A2,"dddd"), or format the date cell with dddd.
Does WEEKDAY count holidays or tell me if a date is a workday?
No. WEEKDAY only identifies a date’s position in the week. To count workdays over a range and exclude listed holidays, use NETWORKDAYS with the relevant date range and holiday cells.
Related
- Average Date Calculation in Excel: Methods and Examples
- Auto-Update Calendar Excel Template: Setup and Fixes
- Autofit Button in Excel: Where It Is and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Conditional Formatting Formula for DatesIn Excel for Microsoft 365 on Windows, use a formula-based conditional formatting rule to highlight dates that meet…
- Excel COUNTIF Specific Cells: How to Count Only Certain CellsTo count only specific cells in Excel, use the COUNTIF function with a range and a condition, like…
- Excel Cell Color by Hex: Apply, Match, and DisplayYes. In Excel for Microsoft 365, you can apply a cell fill that corresponds to a hex color,…
- Excel Find and Replace Within a Formula: Steps and TipsYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options if needed, set Look in to…
- Excel Formula for Next Year Calendar?The simplest Excel formula for a next-year calendar is =DATE(YEAR(TODAY())+1,1,1), which returns the first day of next year…
- Excel Median Formula: How to Calculate the Middle ValueUse Excel’s MEDIAN function to find the middle number in a set: if your values are in A2:A10,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.