Excel Formula for Weekday: Get Day of Week

Formulas & functions · Updated November 22, 2026

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=6
  • 11 → 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:

  1. Select the range (e.g., A2:A100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. Enter: =OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7) (assuming default return type).
  5. 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). Use return_type 21 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.