When working with dates in Excel, you may sometimes have a need to highlight only specific days of the week.
For example, you may have a list of dates where you want to highlight all the weekend dates.
This can easily be done using Conditional Formatting with a little bit of formula know-how.
In this tutorial, I will show you how to highlight the weekend dates in a date data set. The method covered here can also be used to highlight any kind of dates (say Mondays or Tuesdays or alternate days)
So let’s get to it!
Highlight Weekend Dates Using Conditional Formatting
Conditional Formatting in Excel allows you to assess the value in a cell and then apply it to format if the specified condition is met.
We can use Conditional Formatting to analyze the date in a range of cells, and if the date lies on a weekend, highlight it.
Below I have a dates dataset where I want to highlight all the dates that occur on a Saturday or a Sunday.
Below are the steps to do this:
- Select the cells that contain the dates
- Click the Home tab
- In the Styles group, click on Conditional Formatting icon
- In the options that appear, click on ‘New Rule’ option
- In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’
- Enter the following formula in the formula field: =WEEKDAY(B2,2)>5
- Click the Format button
- Specify the formatting (I will go with the Yellow fill color)
- Click OK
- Click OK
The above steps would highlight those dates that are either a Saturday or a Sunday.
How does this work?
I have used a formula in Conditional Formatting that checks each cell in the selected range against that formula.
If the result of that formula is TRUE, then the cell is highlighted in the specified color (yellow in this example), and if the result of the formula is FALSE, then nothing happens.
I had used the WEEKDAY formula that takes the date as the input and returns a value that tells me what day of the week that date represents.
For example, if it is a Monday it would return 1 and if it is a Tuesday it would return 2, and so on. For Saturday and Sunday, it returns 6 and 7 respectively.
And since I have the formula, =WEEKDAY(B2,2)>5, it checks whether the weekday value for a date is more than 5 or not. This would only return TRUE for those dates that occur on a Saturday or Sunday.
So, only those dates that occur on weekends (Sat or Sun) are highlighted.
The same steps (covered above) with a slight change in formula can also be used to highlight only Sunday dates or any specific day of the week.
Highlight Only Sunday Dates
Use the below formula in Conditional Formatting to only highlight dates that occur on Sunday:
=WEEKDAY(B2,2)>6
Highlight Specific Days of the Week
In case you want to highlight specific days of the week, you can do that using a simple OR formula with the WEEKDAY formula in conditional formatting.
Below is the formula that will highlight only those dates that occur on a Tuesday or a Thursday:
=OR(WEEKDAY(B2,2)=2, WEEKDAY(B2,2)=4)
The above OR formula checks whether the WEEKDAY formula for the date returns 2 or 4 (where 2 is for Tuesday and 4 is for Thursday).
And in case any of these two WEEKDAY formulas return a TRUE, the OR formula also returns a TRUE.
So this is how you can use a simple formula in Conditional Formatting to highlight weekend dates (or specific weekdays) in Excel.
I hope you found this tutorial useful!
Other Excel tutorials you may also like:
- Get Day Name from Date in Excel
- How to Add or Subtract Days to a Date in Excel (Shortcut + Formula)
- Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)
- How to Highlight Blank Cells in Excel
- Highlight the Active Row and Column in a Data Range in Excel
- How to Calculate the Number of Days Between Two Dates in Excel
- How to Autofill Only Weekday Dates in Excel (Formula)
- Highlight Dates Before Today in Excel
