Khan Muhammad Kawser
In this tutorial Excel Conditional Formatting Dates, you will learn how to use the Excel function WEEKDAY() to find out the weekdays from some dates.
In this tutorial, you have been introduced to basic conditional formatting.
Master Excel Formulas & Functions in Just 3.5 Hours!
with my FREE COURSE at Udemy.
Excel Formulas and Functions with Excel Formulas Cheat Sheet!
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
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.
➥Related: Excel Formula to Change Cell Color Based on Date
Download Exercise Files, Video Summary & Practice Problems
Download Exercise Files, Video Summary & Practice Problems
Video Transcript
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 the 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 the 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 Sunday and Saturday I mean the weekends on these dates. I select a date, click on the conditional formatting drop-down in the Home ribbon, select the 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 the 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 the 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.
