In this tutorial Excel Conditional Formatting Dates, you will learn how to use Excel function WEEKDAY() to find out the weekdays from some dates.
You might want to look through the other posts related to Excel Conditional Formatting Formula:
- Excel Conditional Formatting Formula
- Excel Conditional Formatting Based on Another Cell
- Excel Alternating Row Color with Conditional Formatting
Table of Contents
Excel Conditional Formatting Dates – Find out the Weekdays from Some Dates
In this video lecture:
- You will learn how to highlight the weekends from some dates using formula based conditional formatting.
- And you will practically use two functions: WEEKDAY() and OR() functions.
Download Exercise Files, Video Summary & Practice Problems
In this video tutorial, we shall see how you can highlight weekends from some dates using formula based conditional formatting. To understand this example, you have to know the syntax of two functions and you’ve to know how to use them. They are: Weekday function and OR function. Weekday and OR functions are covered in “advanced formulas” section. To find out the exact video number, you can use our “Search Topic Dashboard”.
You are seeing a worksheet. You see there are some dates in this worksheet. The weekends Saturday and Sunday are highlighted with yellow background color. Different countries have different weekends. For this example, I am taking Saturday and Sunday as the weekends. You know if we use weekday function without any return type value, then 1 is returned for Sunday, and 7 is returned for Saturday. And you know about OR function. If any logical expression is true, the OR function returns True, if all the expressions are False, the function returns false. The same dates are copied to the next worksheet. OK, let’s highlight the Sunday and Saturday I mean the weekends in these dates. I select a date, click on the conditional formatting drop-down in the Home ribbon, select New Rule option from the list, New Formatting Rule dialog box appears.
I select the last Rule type in the list: “Use a formula to determine which cells to format”. In the formula field I type: equal to OR, Open parenthesis, weekday, opening parenthesis, cell A3, closing parenthesis, equal to 1, comma, weekday, A3, equal to 7, closing parenthesis. Click on the Format button. I just use Yellow background to highlight cells. So how this formula works? If A3 is Sunday, the weekday (A3) returns 1, 1 equal 1 is true, or if A3 is Saturday, weekday (A3) returns 7, 7 equal to 7 is true. For this case A3 is actually Thursday, so weekday(a3) returns 5, 5 is not equal to 1, so False, 5 is also not equal to 7, so both are false, So the OR function returns False. When this Field generates False, no formatting happens. I click OK; you see no formatting happens, as Thursday is not a weekend. I click on Format Painter tool, and select all the dates. The format applied to cell A3 is copied to all other cells. You see the dates that are either Saturday or Sunday are highlighted with yellow background. So this is how you can highlight weekends of some dates and you can’t achieve this type of highlighting with default formatting options. So this is how you can highlight weekends of some dates.