The WEEKDAY function tells you which day of the week a date falls on. It returns a number — 1 through 7 — that you can use in IF tests, conditional formatting, and calculations. For example, you can check whether a date is a Saturday or Sunday, apply a weekend pay rate, or highlight non-working days in a schedule. WEEKDAY works in all Excel versions and requires no dynamic array support.
What Is the WEEKDAY Syntax?
The function takes two arguments. Only the first is required.
| Argument | Required? | What it does |
|---|---|---|
| serial_number | Required | The date you want to evaluate. Use a cell reference, DATE(), or TODAY(). You can also pass a date as text in quotes, but a cell reference is safer and more reliable. |
| return_type | Optional | Controls which day is numbered 1 and which is 7. The default (1 or omitted) treats Sunday as 1 and Saturday as 7. Use 2 to treat Monday as 1 and Sunday as 7. See the full table below for all options. |
What Does the return_type Argument Do?
The return_type argument changes how the numbers map to days. This matters when you write weekend checks. The default assigns 1 to Sunday and 7 to Saturday. However, many business users prefer return_type 2, which assigns 1 to Monday and 7 to Sunday. With type 2, weekends are always 6 and 7 — a simpler test.
| Code | Week start | Numbers assigned (1 → 7) |
|---|---|---|
| 1 | Default (omit) | Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7 |
| 2 | Monday first | Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7 |
| 3 | Monday first (0-based) | Mon=0, Tue=1, Wed=2, Thu=3, Fri=4, Sat=5, Sun=6 |
| 11 | Monday first | Mon=1 … Sun=7 (same as 2, Excel 2010+) |
| 12 | Tuesday first | Tue=1 … Mon=7 (Excel 2010+) |
| 13 | Wednesday first | Wed=1 … Tue=7 (Excel 2010+) |
| 14 | Thursday first | Thu=1 … Wed=7 (Excel 2010+) |
| 15 | Friday first | Fri=1 … Thu=7 (Excel 2010+) |
| 16 | Saturday first | Sat=1 … Fri=7 (Excel 2010+) |
| 17 | Sunday first | Sun=1 … Sat=7 (same as 1, Excel 2010+) |
WEEKDAY(A2,2) > 5 returns TRUE for any Saturday or Sunday.
Example 1: Identify Weekdays and Weekends
The most common use is to check whether a date is a weekday or a weekend. You can test for Saturday and Sunday using an OR formula, or use the simpler greater-than check with return_type 2.
return_type = 2 (Mon=1 … Sun=7)
Example 2: Get the Day Name from a Date
WEEKDAY returns a number, not a name. You can convert that number to a day name in two ways. The simplest is to use TEXT() directly on the date. For more control — such as custom abbreviations or translated names — use CHOOSE() with WEEKDAY as the index.
Example 3: Weekend Pay Rate and Conditional Calculations
WEEKDAY is ideal for payroll and billing calculations. You can check whether a date is a weekend and apply a different multiplier. This way, the same formula handles both standard and premium rates without any manual editing.
Example 4: Conditional Formatting to Highlight Weekends
Conditional formatting rules can use WEEKDAY to automatically shade weekend columns or rows in a schedule. This gives your calendar a professional look and makes non-working days immediately visible. The rule applies to the entire selected range and updates as dates change.
$A2 — dollar on the column, no dollar on the row. This locks the formula to column A while allowing it to check each row as you apply the rule across the whole range.
Example 5: Snap a Date to the Next Working Day
Sometimes a calculated date lands on a Saturday or Sunday. In those cases, you need to move it forward to the next Monday. WEEKDAY lets you detect the weekend day and add the right number of days to reach Monday. This pattern is useful for due dates, delivery dates, and payment schedules.
Example 6: Count and Sum by Day of Week
Analysing sales or attendance by day of week is a common reporting task. WEEKDAY combined with SUMPRODUCT lets you count or sum for a specific day across a date range. You can also use this pattern in a pivot-style summary to show totals for every day of the week at once.
=SUMPRODUCT((WEEKDAY(A:A,2)=1)*B:B). This works in all Excel versions without pressing Ctrl+Shift+Enter.
How to Fix Common WEEKDAY Errors
Result shows a date instead of a number
The cell is formatted as a Date. Select the cell, press Ctrl+1, and choose Number or General from the Number tab. WEEKDAY always returns a plain integer (1–7), so any Date format will misrepresent it.
#VALUE! error
The serial_number argument is not a valid date. A cell that looks like a date but stores the value as text will trigger this error. Test the cell with =ISNUMBER(A2). If it returns FALSE, re-enter the date properly or use DATEVALUE() to convert it.
Wrong day returned — off by one
This usually means a return_type mismatch. The default assigns 1 to Sunday, not Monday. If your formulas assume Monday = 1, add return_type 2 to every WEEKDAY call. Consistency across all formulas in the same workbook prevents this confusion.
Frequently Asked Questions About WEEKDAY
-
What does the WEEKDAY function return in Excel?WEEKDAY returns a number from 1 to 7 that represents the day of the week for a given date. By default, 1 = Sunday and 7 = Saturday. You can change this mapping with the optional return_type argument. For example, return_type 2 assigns 1 to Monday and 7 to Sunday, which makes weekend checks simpler.
-
How do I check whether a date is a weekend with WEEKDAY?The cleanest way is to use return_type 2 so weekends are 6 and 7, then test whether the result is greater than 5: =IF(WEEKDAY(A2, 2) > 5, "Weekend", "Weekday"). Alternatively, with the default type, test for 1 (Sunday) or 7 (Saturday): =IF(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7), "Weekend", "Weekday").
-
How do I get a day name from a date in Excel?The simplest way is TEXT(A2, "dddd") for a full name like "Thursday", or TEXT(A2, "ddd") for a short name like "Thu". You do not need WEEKDAY for this. If you need a custom label — for example, "Weekend" for Saturday and Sunday — use CHOOSE(WEEKDAY(A2, 2), "Mon","Tue","Wed","Thu","Fri","Weekend","Weekend").
-
Which return_type should I use with WEEKDAY?For most business use cases, return_type 2 (Monday = 1, Sunday = 7) is the most intuitive. Weekdays are 1–5 and weekends are 6–7, which makes comparison formulas easy to read. The default type 1 (Sunday = 1) is inherited from older US-centric Excel conventions and can lead to confusion when writing weekend logic. Use type 1 only if you have a specific reason to treat Sunday as the first day of the week.
-
How do I sum values by day of the week?Use SUMPRODUCT with a WEEKDAY comparison: =SUMPRODUCT((WEEKDAY(A2:A100, 2)=1) * B2:B100). This sums all values in column B where the corresponding date in column A is a Monday (1 with return_type 2). Change =1 to =2 for Tuesdays, =6 for Saturdays, and so on. SUMPRODUCT handles array logic without needing Ctrl+Shift+Enter.
-
Which Excel versions support WEEKDAY?WEEKDAY works in all Excel versions from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013, 2010, and 2007. It also works in Excel for the web. Return_type codes 11 through 17 require Excel 2010 or later — use codes 1, 2, or 3 for full backwards compatibility.