The core Excel formula for a weekday is =WEEKDAY(date), which returns a number from 1 (Sunday) to 7 (Saturday) by default. To get the actual day name, use =TEXT(date,"dddd") or =CHOOSE(WEEKDAY(date),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday").
Understanding the WEEKDAY Function
The WEEKDAY function is Excel’s built-in tool for identifying the day of the week. Its syntax is:
=WEEKDAY(serial_number, [return_type])
Here, serial_number is the date you want to evaluate (either a cell reference like A2 or a date entered with DATE(2025,1,15)). The optional return_type controls how the days are numbered. If you omit it, Excel uses 1 for Sunday through 7 for Saturday. But you can change that:
2→ Monday=1, Tuesday=2, … Sunday=7 (common in many countries)3→ Monday=0, Tuesday=1, … Sunday=611→ Monday=1, Tuesday=2, … Sunday=7 (same as 2, but for ISO week numbers)15→ Friday=1, Saturday=2, … Thursday=7 (for fiscal weeks starting Friday)
For example, with the date 2025-01-15 in cell A2:
=WEEKDAY(A2)
returns 4 (Wednesday, since Sunday is 1). With =WEEKDAY(A2,2), it returns 3 (Monday=1, so Wednesday is the third day).
Converting a Number to a Day Name
Often you don’t want a number; you want the word “Wednesday.” Two straightforward approaches exist.
Using TEXT: The TEXT function formats the date as text.
=TEXT(A2,"dddd")
This returns the full day name, e.g., “Wednesday.” Use "ddd" for the abbreviated form (“Wed”). However, TEXT returns a text string, so you can’t use the result in further date arithmetic (like sorting by weekday).
Using CHOOSE: This gives you full control over the output, including custom labels.
=CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
Replace the abbreviations with anything you like, such as “Weekend” or “Workday” for specific days. This works because CHOOSE picks the item at the index given by WEEKDAY.
Using WEEKDAY in Conditional Formatting
You can highlight weekends or specific weekdays using conditional formatting with a formula. For example, to highlight all Saturdays and Sundays in a range A2:A100:
- Select the range (e.g., A2:A100).
- Go to Home > Conditional Formatting > New Rule.
- Choose “Use a formula to determine which cells to format.”
- Enter:
=OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7)(assuming default return type). - Click Format to set a fill color, then OK.
For a workday highlight (Monday–Friday), use =AND(WEEKDAY(A2,2)<=5). The formula applies to the top-left cell of the selection, and Excel adjusts it for the rest.
Common Problems and Pitfalls
Problem 1: Dates stored as text. If your “date” is actually text like “2025-01-15”, WEEKDAY may return #VALUE!. Fix by converting to a real date using DATEVALUE or by using Data > Text to Columns.
Problem 2: Wrong weekday due to system locale. The default return_type (1) assumes Sunday is the first day. In many regions, Monday is considered the start. Always specify 2 if you want Monday=1 to avoid confusion.
Problem 3: TEXT returns a text string. If you need to sort by weekday, a text result sorts alphabetically (Friday, Monday, Saturday…), not chronologically. Instead, keep the numeric WEEKDAY value in a helper column and sort by that.
Problem 4: Blank cells. If a cell is empty, WEEKDAY returns 7 (because Excel treats blank as 0, which is Saturday in default numbering). Wrap with IF: =IF(A2="","",WEEKDAY(A2)).
Alternatives: WEEKNUM, ISOWEEKNUM, and Custom Formulas
While WEEKDAY gives the day of the week, related functions answer other questions:
WEEKNUM(date, [return_type])returns the week number of the year (1–52). Usereturn_type21 for ISO week numbers (Monday start).ISOWEEKNUM(date)always returns the ISO week number (Monday-based, week 1 contains the first Thursday of the year).- To find the date of the next Monday after a given date, use
=A2+MOD(8-WEEKDAY(A2,2),7)(where Monday=1). - To count weekdays between two dates, use
NETWORKDAYS(start_date,end_date), which excludes Saturdays and Sundays by default.
For a custom day name that includes the date, combine TEXT: =TEXT(A2,"dddd, mmmm d, yyyy") gives “Wednesday, January 15, 2025”.
Working with Dates in Different Excel Versions
The WEEKDAY function works identically in Excel 2010 through Excel 2021 and Microsoft 365. However, if you use LET or LAMBDA to build more complex weekday logic, those require Excel 2021 or Microsoft 365. For example:
=LET(d,A2,CHOOSE(WEEKDAY(d),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"))
This is just a more verbose version of the CHOOSE formula, but it shows how LET can make longer formulas easier to read. On Mac, the same formulas work, but the keyboard shortcut for conditional formatting is Cmd+1 after selecting the range (to open Format Cells), and you access Conditional Formatting via the Home tab as well. Excel for the web also supports WEEKDAY and TEXT fully.
Frequently Asked Questions
How do I get the weekday as a number with Monday as 1?
Use =WEEKDAY(A2,2). This returns 1 for Monday, 2 for Tuesday, and so on up to 7 for Sunday. This is the most common alternative to the default Sunday=1.
Why does my WEEKDAY formula return #VALUE!?
Usually because the cell contains text that Excel can’t interpret as a date. Check that the cell is formatted as a date. If it’s text, use =WEEKDAY(DATEVALUE(A2)) or convert the text to a date using Data > Text to Columns.
Can I use WEEKDAY to find the day of the week for a date in the past or future?
Yes, WEEKDAY works for any date that Excel recognizes, from January 1, 1900 to December 31, 9999. For example, =WEEKDAY(DATE(2030,12,25)) returns 4 (Wednesday, since 2030-12-25 is a Wednesday).
Is there a way to get the weekday name without using TEXT?
Yes, use =CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"). This gives you a text result that you can customize, but unlike TEXT, it doesn’t depend on the cell’s number format.
How do I highlight only weekends in a column?
Select the column, go to Home > Conditional Formatting > New Rule, choose “Use a formula,” and enter =OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7) (assuming A2 is the first cell). Set a fill color and click OK.
Does WEEKDAY work with times?
Yes, but the time part is ignored. WEEKDAY only looks at the date portion of a date-time value. For example, =WEEKDAY("2025-01-15 14:30") still returns 4 for Wednesday.
Practical Example: Building a Weekly Schedule
Suppose you have a list of dates in column A (A2:A10) and you want to label each as “Workday” or “Weekend” in column B. Enter this in B2 and copy down:
=IF(OR(WEEKDAY(A2,2)>5),"Weekend","Workday")
Here, WEEKDAY(A2,2) returns 6 for Saturday and 7 for Sunday, so the OR condition catches both. The result will be “Weekend” for those days, “Workday” otherwise. This is a clean way to categorize dates without complex nested IFs.
Related
- Excel Date Minus Date to Get Months
- Excel Data Validation Must Be a Date? No—Here’s How
- Excel Data Validation Start Date: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Filter Duplicates in Excel: Keep One or Remove All?To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data >…
- Excel Sum by Date: Formulas for Daily and Monthly TotalsTo sum values for a date in Excel, use SUMIF with the date column as the criteria range…
- Oracle SQL Left String Function: Use SUBSTR InsteadOracle SQL does not provide a built-in LEFT() string function. To return the first characters of a value,…
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel Conditional Formatting Formula if BlankThe formula to highlight blank cells in Excel conditional formatting is =ISBLANK(A1) (or =A1=""), applied to the range…
- Attendance Time Calculation Formula in Excel: A GuideFor clock-in time in A2 and clock-out time in B2, calculate attendance duration with =MOD(B2-A2,1) and format the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.