If you’ve ever needed to figure out what day of the week a given date falls on in Excel, you’re in luck—there’s a built-in function just for that. The WEEKDAY function makes it easy to determine the weekday as a number, which you can then use in formulas, conditional formatting, or reports. Let’s dive into how WEEKDAY works in Excel and explore some of the best WEEKDAY examples.
What Is the WEEKDAY Function?
The WEEKDAY function in Excel returns a number representing the day of the week for a given date. By default, Sunday is considered the first day of the week (1), and Saturday is the last (7). However, you can adjust this using the optional return_type parameter.
WEEKDAY Syntax
The syntax of the WEEKDAY function is straightforward:
=WEEKDAY(serial_number, [return_type])- serial_number – The date you want to analyze (must be a valid Excel date).
- return_type (optional) – Defines which number corresponds to which day of the week.
Understanding Return Types
Excel provides multiple return types, which determine how the weekdays are numbered. Here’s a quick overview:
| Return Type | Week Starts On | Week Ends On |
|---|---|---|
| 1 (default) | Sunday = 1 | Saturday = 7 |
| 2 | Monday = 1 | Sunday = 7 |
| 3 | Monday = 0 | Sunday = 6 |
| 11 | Monday = 1 | Sunday = 7 |
| 12 | Tuesday = 1 | Monday = 7 |
| 13 | Wednesday = 1 | Tuesday = 7 |
| 14 | Thursday = 1 | Wednesday = 7 |
| 15 | Friday = 1 | Thursday = 7 |
| 16 | Saturday = 1 | Friday = 7 |
| 17 | Sunday = 1 | Saturday = 7 |
Basic WEEKDAY Example
Let’s say I have a date in cell A1, and I want to find out what day of the week it represents:
=WEEKDAY(A1)By default, this will return a number between 1 (Sunday) and 7 (Saturday).
Changing the Week Start Day
What if I want Monday to be the first day of the week instead of Sunday? I can do this using the return_type argument:
=WEEKDAY(A1, 2)Now, Monday is 1, Tuesday is 2, and so on, ending with Sunday as 7.
How to Use WEEKDAY in Conditional Formatting
One of my favorite ways to use WEEKDAY is for conditional formatting. Let’s say I want to highlight all weekends in a column of dates. I can apply a conditional formatting rule using this formula:
=OR(WEEKDAY(A1,2)=6, WEEKDAY(A1,2)=7)This checks if the day is a Saturday (6) or Sunday (7) and highlights the cell accordingly.
Extracting the Day Name Using WEEKDAY
If I want to return the actual day name (e.g., “Monday” instead of just a number), I can use WEEKDAY with the CHOOSE or TEXT function.
Using CHOOSE
=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")Using TEXT
=TEXT(A1, "dddd")The second method is neater because it dynamically pulls the full day name based on A1’s value.
Identifying Weekends and Weekdays
If I need to check whether a date falls on a weekend or a weekday, I can use:
=IF(WEEKDAY(A1,2)<=5, "Weekday", "Weekend")This formula returns “Weekday” for Monday to Friday and “Weekend” for Saturday and Sunday.
Using WEEKDAY in Payroll and Scheduling
In real-world applications, WEEKDAY is useful for payroll processing, staffing schedules, or automating work shifts. For example, if I only want to count business days (Monday-Friday) in a given period, I can combine WEEKDAY with SUMPRODUCT:
=SUMPRODUCT((WEEKDAY(A1:A10,2) <= 5)*1)This counts the number of business days in the specified date range.
Common Errors and How to Fix Them
- #VALUE! Error: This usually happens when
serial_numberisn’t a valid date. Make sure the cell contains a real date, not text. - Incorrect Output: If the wrong day is returned, check the
return_typeargument. Different return types shift the weekday numbering.
Final Thoughts
Understanding how WEEKDAY works in Excel opens up a world of possibilities. Whether I need to format reports, filter data, or automate schedules, this function is a simple yet powerful tool. The best WEEKDAY examples often involve combining it with other functions like IF, CHOOSE, or TEXT for maximum efficiency.
Other interesting article:
How SECOND works in Excel? Best SECOND examples