To get the weekday from a date in Excel, use the WEEKDAY function: =WEEKDAY(A2) returns a number from 1 (Sunday) to 7 (Saturday). For the weekday name, combine it with TEXT: =TEXT(A2,"dddd") returns the full name, or =TEXT(A2,"ddd") for the abbreviated name. This article explains both approaches, how to customize the numbering, and how to handle common issues like regional date formats.
Why the WEEKDAY Function Matters
Excel stores dates as serial numbers – each date is an integer counting days from January 1, 1900. The WEEKDAY function converts that serial number into a day-of-week number, which is the foundation for scheduling, payroll, and reporting. Without it, you would have to manually look up each date, which is error-prone. The function works in all modern versions of Excel, including Microsoft 365, Excel 2021, Excel 2019, and Excel for the web.
How the WEEKDAY Function Works
The syntax is =WEEKDAY(serial_number, [return_type]). The first argument is the date (a cell reference or a date entered with DATE()). The optional second argument controls which day is numbered 1. The default (omitted) treats Sunday as 1, but you can specify other systems:
- 1 or omitted: Sunday=1, Saturday=7 (default)
- 2: Monday=1, Sunday=7 (common for international weeks)
- 3: Monday=0, Sunday=6
- 11-17: For ISO week numbers (Monday=1, Sunday=7 for 11; Tuesday=1 for 12, etc.) – available in Excel 2010 and later.
For example, with the date 2025-03-15 (a Saturday) in cell A2:
=WEEKDAY(A2)returns 7 (Saturday is the 7th day when Sunday=1).=WEEKDAY(A2,2)returns 6 (Saturday is the 6th day when Monday=1).
Getting the Weekday Name (Text) from a Date
If you need the name of the day (e.g., “Monday”) instead of a number, use the TEXT function. The format codes are:
"dddd"– full name (e.g., “Monday”)"ddd"– abbreviated name (e.g., “Mon”)
Example: With date in A2, =TEXT(A2,"dddd") returns “Saturday” for 2025-03-15. This works in any Excel version, but note that TEXT returns text, not a number, so you cannot easily use it in arithmetic or sorting by day order. For sorting, it’s better to keep the numeric WEEKDAY result and then use a lookup table for names.
Choosing the Right Return Type
The return_type argument is crucial for accurate results. Here’s how to decide:
- Default (1): Use if your week starts on Sunday (typical in the US).
- 2: Use if your week starts on Monday (common in Europe, ISO 8601).
- 3: Use if you want Monday=0, which is handy for calculating workdays since Monday is often day 0 in some formulas.
- 11-17: Use for ISO week numbers, where Monday is the first day of the week. For example,
WEEKDAY(A2,11)gives Monday=1, Tuesday=2, …, Sunday=7.
If you omit return_type, Excel assumes 1. Always test with a known date to verify your choice.
Common Problems and How to Fix Them
Here are frequent issues users encounter:
- Wrong day number: If your week starts on Monday, you must use
return_type2. Forgetting this is the most common mistake. - Date stored as text: If your “date” is actually text like “15/03/2025”,
WEEKDAYmay return#VALUE!. Convert text to a real date usingDATEVALUEor the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Date). - Regional date formats: Excel reads dates according to your system’s locale. If you type a date like “03/15/2025” in a region that uses day/month, it may be interpreted as March 15 or May 3. Use the
DATEfunction to avoid ambiguity:=WEEKDAY(DATE(2025,3,15))always returns 7. - Weekday name in another language:
TEXTuses the system’s language. To get English names regardless of locale, use a lookup table:=CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday").
Alternatives: WORKDAY and NETWORKDAYS
While WEEKDAY identifies the day, other functions build on it for scheduling:
WORKDAY(start_date, days, [holidays])– returns the date after a given number of working days (skips weekends, optionally holidays).NETWORKDAYS(start_date, end_date, [holidays])– counts the number of working days between two dates.WEEKNUM– returns the week number of the year, which uses a similarreturn_typeargument.
Example: To find the date 10 working days after March 15, 2025, use =WORKDAY(DATE(2025,3,15),10). This returns March 28, 2025 (skipping weekends).
Frequently Asked Questions
How do I get the weekday as a name like “Monday”?
Use =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for the abbreviation. This works in all Excel versions, but the result is text, so you cannot perform arithmetic on it. For sorting, use the numeric WEEKDAY result.
Why does my WEEKDAY formula return #VALUE!?
This usually means the cell contains text that looks like a date, not a real Excel date. Convert the text to a date using DATEVALUE or the Text to Columns feature. Also check that the date is not entered as a formula that returns text.
Can I make Monday the first day of the week?
Yes, use =WEEKDAY(A2,2). This is the ISO standard and is common in many countries. For ISO week numbers, use =WEEKNUM(A2,21).
How do I count weekdays between two dates?
Use =NETWORKDAYS(start_date, end_date). This excludes Saturdays and Sundays by default. To exclude holidays, add a range of holiday dates as the third argument.
What is the difference between WEEKDAY and WEEKNUM?
WEEKDAY returns the day of the week (1-7), while WEEKNUM returns the week number of the year (1-52/53). They serve different purposes: WEEKDAY for scheduling within a week, WEEKNUM for annual reporting.
Does WEEKDAY work with dates before 1900?
Excel’s date system starts from January 1, 1900. Dates before that are not supported as real dates. If you have pre-1900 dates, you’ll need to treat them as text and handle them manually.
Related
- Excel Templates for Time Tracking: Best Free Options
- Profit and Loss Excel Templates: How to Use Them
- Excel Template Sales Tracker: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: Exclude Text from a CountTo count nonblank cells while excluding any cell that contains a particular word, use =COUNTIFS(A2:A100,"<>",A2:A100,"<>*apple*"). Here, the values…
- EDATE in Excel: How to Add Years to a Date CorrectlyExcel’s EDATE function adds months, not years. To add a whole number of years to a date in…
- Excel LEFT of a Character: Formula and ExamplesTo return everything to the left of a character in Excel, combine LEFT with FIND: with the text…
- How to Sort in Excel by Number Order Step by Step GuideTo sort numbers in Excel, select a cell in the number column, then choose Data > Sort Smallest…
- Transpose in Excel and Keep Formulas: A Practical GuideTo transpose a range in Excel and keep its formulas, copy the cells, select a blank destination, then…
- SUMIFS Not Summing Correctly? Excel Causes and FixesSUMIFS usually returns an unexpected total because its ranges do not line up, a criterion does not match…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.