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: 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:
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: 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 ExcelHow 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 ExcelHighlight the Active Row and Column in a Data Range in ExcelHow to Calculate the Number of Days Between Two Dates in ExcelHow to Autofill Only Weekday Dates in Excel (Formula)Highlight Dates Before Today in Excel
