Distinguishing between weekdays and weekends is a common requirement when working with dates in Excel. Whether you're designing a dynamic calendar, automating reporting, or managing project schedules, being able to identify weekends can greatly simplify your logic and formatting rules.

Excel provides several built-in date functions to support these needs, the most relevant of which is the WEEKDAY function.

Understanding the WEEKDAY Function

The WEEKDAY function returns an integer between 1 and 7, representing the day of the week for a given date. However, the meaning of this value depends on the optional second argument — and misunderstanding it is a frequent source of errors ⚠️

The syntax is as follows:

=WEEKDAY(serial_number, [return_type])

If the second argument (return_type) is omitted, Excel defaults to 1. In this configuration:

• Sunday = 1
• Monday = 2
• ...
• Saturday = 7

This default reflects the U.S. convention where the week starts on Sunday. However, in many countries — particularly in Europe — Monday is considered the first day of the week. To align with that and simplify logical operations, it's best to set the second argument to 2 explicitly.

• Monday = 1
• Tuesday = 2
• ...
• Saturday = 6
• Sunday = 7

To simplify weekend testing, setting the second argument to 2 is best. Like that, writing a logical test to test if a day is a weekday or a weekend becomes easy 😀👍

How to test if a date is a weekend?

To check if a date is a weekend or not, you can write this simple test:

=WEEKDAY(Date,2)>5

• If the WEEKDAY function returns 6, it indicates Saturday
• And if the result is 7, the day is Sunday.
• Utilizing this formula, you can determine whether a day falls on a weekend or a weekday.

Omitting the second argument in WEEKDAY makes the test more complex, requiring an OR function to check for 1 or 7.

Test if a date is a Weekday

In the same way, to test if a date is a weekday or not, you can write this formula. This time, you must include the value 5 (Friday) in your result using the 'less than or equal to (<=)' operator.

=WEEKDAY(Date,2)<=5

Conditional formatting

To highlight the day of the weekend in RED you can use the same formula inside a conditional formatting rule.

1. Copy your test (weekend or weekday)
2. Select all the cells of your document
3. Open the menu Home>Conditional Formatting>Manage Rules>New Rules
4. Select the option Use a formula to determine which cells to format
5. Paste the formula
6. Change the format

With this trick, you can highlight the entire row when the day is a weekend.

Related Articles

• Change the Color of Weekends in Excel 🗓️🎨
• Add Days Excluding the Weekend in Excel
• First day – Last day in Excel
• Create a weekly calendar with just one formula in Excel
• Change the color of holidays
• Calculate Public holidays with Excel 📆
• The WEEKDAY function – Secrets and Errors
• Identifying Weekdays and Weekends with Excel