Excel Conditional Formatting Dates

In this tutorial Excel Conditional Formatting Dates, you will learn how to use Excel function WEEKDAY() to find out the weekdays from some dates.

In this tutorial, you have been introduced with the 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:

  1. Excel Conditional Formatting Formula
  2. Excel Conditional Formatting Based on Another Cell
  3. Excel Alternating Row Color with Conditional Formatting
Learn Excel Online: Top Excel Courses Online

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

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 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.

Read More…

How to Compare Two Columns or Lists in Excel

Excel Conditional Formatting Formula

How to Highlight Every Other Row in Excel

How to Use the NETWORKDAYS vs. the NETWORKDAYS.INTL Function

Excel Conditional Formatting Based on Another Cell Text

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

1 Comment

      Leave a reply