Highlight Weekend Dates In Your Data Set. Conditional Formatting Using A Formula – Formula Friday


Hello Excellers and welcome to another #formulafriday blog post. Today let’s take a look at using conditional formatting along with an Excel formula to highlight dates in our data set that fall on a weekend.

As part of understanding data trends in your analysis, you may need to know what dates in your data fall at the weekend.  You may be looking at website traffic and the volumes of traffic,  and as a result, this may be very different on the weekends.  It would be useful to visually see these dates highlighted in my example below.  Weekends are highlighted in green.

We can get this effect by using conditional formatting.  We use the option of using a formula to determine which cells to format.
  • Select the data cells which are to be your target range.  In this example, it is A3:A12
  • Home Tab | Styles Group | Conditional Formatting
  • New Rule | Use a formula to determine which cells to format
Excel will subsequently evaluate the values based on this formula, if the value evaluates to TRUE then the conditional formatting is applied to the cell.
  • In the formula input box enter the formula below

We use the WEEKDAY function to evaluate the WEEKDAY number of the target cell A3.  Therefore, if the target cell returns 1 or 7 then the date falls on a weekend and the cells will be formatted with our choice of design.

WEEKDAY Explained

Returns the day of the week corresponding to a date. As a result, the day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
SYNTAX
WEEKDAY(serial_number,[return_type])
Where
Serial_number Required. A sequential number that represents the date of the day you are trying to find.Return_type Optional. A number that determines the type of return value.  The options are below.  If this is omitted then the value 1 is used.

Finally, to finish off the formatting select the Format Cells dialog box where all of the formatting options are available.  In this example, I have chosen to fill the cells green if the formula evaluates as TRUE.  That’s it, a simple way to highlight weekend dates in your Excel data set.

 

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

1

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

More Excel Tips_New1
Learn Excel With Our Excel Online Courses

Do You Need Help With An Excel Problem?.

I am pleased to announce I have teamed up with Excel Rescue,  where you can get help with Excel FAST.

ExcelRescue.net

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts