To highlight dates that occur on weekends in Excel, we can apply conditional formatting based on the WEEKDAY function. Excel WEEKDAY function helps you identify the day of the week for the date specified. And with a simple conditional formatting rule, we can easily highlight weekend dates in the Excel dataset.
The approach we are learning in this tutorial is not only to highlight weekends, but any day of the week i.e. Monday, Wednesday, or Friday as the Excel WEEKDAY function lets you choose the specific day to highlight.
In this tutorial:
Highlight Weekend Dates in Excel with Conditional Formatting
Following is the example dates dataset and we need to highlight dates that are weekends i.e. Saturday or Sunday.
Here are the steps to do it:
1. Select the entire data range containing dates. In this example it is B2:B16
2. Go to Home tab > under styles group > click conditional formatting drop-down button > click “New Rule…”
3. In the dialogue box click “Use a formula to determine which cells to format”
4. In the input bar put the following formula:
=WEEKDAY(B2,11)>5
5. Click the Format button to open the cell formatting dialogue box.
6. Click the Fill tab and select the desired color. Press OK on the dialogue boxes to apply the conditional format to highlight dates that occur on weekends.
After the above steps, dates that are either Saturday or Sunday will be highlighted as follows:
Highlight only Sundays
If you like to apply conditional formatting only on the dates that are Sunday then use the following formula to highlight such dates:
=WEEKDAY(B2,11)=7Highlight 3 day weekend
Some countries around the world are adopting a three-day weekend. Meaning, the weekend will now probably include Friday as well. So we have Friday, Saturday, and Sunday as weekend days. To highlight weekend dates in this situation we need to make a small change to the WEEKDAY formula as follows:
=WEEKDAY(B2,11)>4
Highlight a different set of weekend days
In some countries, Monday is not the first working day of the week. Hence, such countries have a different set of days as a weekend.
For example, some states of the United Arab Emirates have a workweek of Sunday-Thursday. Therefore, Friday and Saturday are the weekend days.
To accommodate the difference, we need to select the relevant return_type parameter of the WEEKDAY function that matches the workweek cycle.
Here is the list of values one can specify for return_type:
| Return_type | Number returned |
|---|---|
| 1 or omitted | From 1 (Sunday) to 7 (Saturday) |
| 2 | From 1 (Monday) to 7 (Sunday) |
| 3 | From 0 (Monday) to 6 (Sunday) |
| 11 | From 1 (Monday) to 7 (Sunday) |
| 12 | From 1 (Tuesday) to 7 (Monday) |
| 13 | From 1 (Wednesday) to 7 (Tuesday) |
| 14 | From 1 (Thursday) to 7 (Wednesday) |
| 15 | From 1 (Friday) to 7 (Thursday) |
| 16 | From 1 (Saturday) to 7 (Friday) |
| 17 | From 1 (Sunday) to 7 (Saturday) |
So countries where the workweek starts with Saturday as the first day, we will use return type of 16 instead of 11. And our formula will be:
=WEEKDAY(B2,17)>5With the above formula, Friday and Saturday will be highlighted as weekend dates in the dataset.
Highlight specific day(s) of the week
If you desire to highlight a specific day or multiple days of the week, you can easily do that as well.
Again, we need to make sure we are using the correct return_type value. Ignoring this important bit will cause issues with the correct identification of days.
Highlight specific day of the week
Returning to our first example where the workweek starts on Monday, we will use 11 as the value for return_type.
Having workweek decided, if we like to highlight Friday, we will use the following formula:
=WEEKDAY(B2,11)=5
Similarly, if we like to highlight Wednesday the formula will be:
=WEEKDAY(B2,11)=3
Highlight multiple specific days of the week
If you desire to highlight more than one day of the week that are not in series, we can take the help of the OR function in Excel and use it with the WEEKDAY function to find and highlight multiple days of the week.
For example, if we like to highlight Wednesday and Friday then the following formula can help:
=OR(WEEKDAY(B2,11)=3,WEEKDAY(B2,11)=5)
In this Excel tutorial, we learned how to use conditional formatting and functions to highlight weekend dates in Excel and specific weekdays in date data.
Hope this tutorial is helpful.
Checkout the following Excel tutorials:
