WEEKDAY returns a number representing the day of the week for a given date. By default, it returns 1 for Sunday through 7 for Saturday, but you can change the numbering with the return_type argument.
=WEEKDAY(serial_number, [return_type])What does WEEKDAY do?
The WEEKDAY function in Excel converts a date into a number that represents the day of the week. For example, it can tell you that a specific date falls on a Monday, represented by 2 (using the default return type). This is useful whenever you need to identify weekends, plan schedules, or analyse data by day of the week.
Dates in Excel are stored as serial numbers, and WEEKDAY extracts the day-of-week component from that serial number. Without WEEKDAY, you might manually check a calendar or use complex formulas. With it, you can quickly build reports that highlight weekends, calculate working days, or group sales by weekday.
Imagine you manage a team and need to ensure that project deadlines don’t fall on weekends. You have a list of due dates, and you want to flag any that land on Saturday or Sunday. WEEKDAY makes this easy: you can write a formula that returns the weekday number and then use conditional formatting or an IF statement to alert you.
When to use WEEKDAY
- Flagging weekends in a schedule or project plan
- Calculating the number of weekdays between two dates
- Grouping sales or activity data by day of the week
- Determining the day of the week for appointments or events
- Creating dynamic labels that show the weekday name
- Adjusting dates to the nearest working day
WEEKDAY syntax
=WEEKDAY(serial_number, [return_type])| Argument | Required? | What to enter |
|---|---|---|
serial_number | Required | The date you want to find the day of the week for. It can be a date value, a reference to a cell containing a date, or a number representing a date. For example, =WEEKDAY("2023-10-05") returns 5 (Thursday) with the default return type. |
return_type | Optional | A number that determines the numbering scheme for the days of the week. If omitted, it defaults to 1 (Sunday=1 through Saturday=7). Common alternatives: 2 (Monday=1 through Sunday=7), 3 (Monday=0 through Sunday=6). For example, =WEEKDAY("2023-10-05", 2) returns 4 (Thursday as the fourth day when Monday is 1). |
What it returns: WEEKDAY returns a number from 1 to 7 (or 0 to 6 depending on return_type) that indicates the day of the week for the given date.
WEEKDAY examples
1 Basic weekday number from a date
This example shows the simplest use: find the weekday number for a single date. The formula =WEEKDAY(B2) uses the default return type, where Sunday is 1 and Saturday is 7. For 2023-10-05 (a Thursday), it returns 5. This helps you quickly see the day of the week without looking at a calendar.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Event | Date | |||
| 2 | Conference | 2023-10-05 | 5 | ||
| 3 | Workshop | 2023-10-07 | |||
| 4 | Meeting | 2023-10-09 | |||
| 5 |
2 Using return_type 2 for Monday as first day
Here we use return_type 2, which numbers days from Monday (1) to Sunday (7). The formula =WEEKDAY(B2, 2) applied to 2023-10-05 (Thursday) returns 4, because Thursday is the fourth day when Monday is 1. This is common in business contexts where the week starts on Monday.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Due Date | |||
| 2 | Report | 2023-10-05 | 4 | ||
| 3 | Presentation | 2023-10-06 | |||
| 4 | Review | 2023-10-08 | |||
| 5 |
3 Flag weekends with IF and WEEKDAY
Combine WEEKDAY with IF to identify weekends. The formula =IF(WEEKDAY(B2, 2)>5, "Weekend", "Weekday") checks if the weekday number (with Monday=1) is greater than 5, meaning Saturday (6) or Sunday (7). For 2023-10-07 (Saturday), it returns "Weekend". This is useful for highlighting non-working days in a schedule.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Project | Deadline | |||
| 2 | Alpha | 2023-10-07 | Weekend | ||
| 3 | Beta | 2023-10-09 | |||
| 4 | Gamma | 2023-10-10 | |||
| 5 |
4 Get the weekday name with TEXT and WEEKDAY
You can convert the weekday number to a name using TEXT or CHOOSE. Here, =TEXT(B2, "dddd") directly gives the full weekday name, but if you need to use WEEKDAY, you could use =CHOOSE(WEEKDAY(B2), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"). For 2023-10-05, it returns "Thursday". This is handy for reports where you want to show the day name.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Order Date | |||
| 2 | 1001 | 2023-10-05 | Thursday | ||
| 3 | 1002 | 2023-10-06 | |||
| 4 | 1003 | 2023-10-07 | |||
| 5 |
Common WEEKDAY errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The serial_number argument is not a valid date or number. | Ensure the cell contains a date or a number that Excel recognises as a date. If it's text, convert it using DATEVALUE or enter it in a recognised date format. |
| #NUM! | The serial_number is out of range for Excel dates (before 1900 or after 9999). | Use a date within the valid range (January 1, 1900 to December 31, 9999). |
| #VALUE! | The return_type argument is not a valid number. | Use one of the supported return_type values: 1, 2, 3, 11, 12, 13, 14, 15, 16, or 17. |
Tips for using WEEKDAY
- Use return_type 2 to make Monday the first day of the week, which is common in business settings.
- Combine WEEKDAY with conditional formatting to highlight weekends in a date column.
- Remember that WEEKDAY returns a number; use TEXT or CHOOSE to display the day name.
- When counting weekdays, use NETWORKDAYS instead of building complex formulas with WEEKDAY.
- Test your formula with a known date to verify the return_type behaviour.
Frequently asked questions
What does the WEEKDAY function return?
WEEKDAY returns a number from 1 to 7 (or 0 to 6 depending on return_type) that represents the day of the week for a given date.
How do I get the day name instead of a number?
Use the TEXT function, e.g., =TEXT(A1, "dddd") for the full name or =TEXT(A1, "ddd") for the abbreviated name. Alternatively, use CHOOSE with WEEKDAY.
Can WEEKDAY handle dates entered as text?
No, WEEKDAY expects a valid date serial number. If your date is text, convert it with DATEVALUE or ensure it's recognised as a date.
What is the difference between return_type 1 and 2?
Return_type 1 (default) numbers days from Sunday=1 to Saturday=7. Return_type 2 numbers from Monday=1 to Sunday=7.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. WEEKDAY reference