How to Highlight Weekend Dates in Excel?

When working with dates in Excel, you may sometimes have a need to highlight only specific days of the week.

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.

Date Dataset to highlight weekend dates

Below are the steps to do this:

  1. Select the cells that contain the dates
Select the cells that have the dates
  1. Click the Home tab
Click the Home tab
  1. In the Styles group, click on Conditional Formatting icon
Click on Conditional Formatting icon
  1. In the options that appear, click on ‘New Rule’ option
Click on New Rule option
  1. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’
Select Use a formula to determine what cells to format
  1. Enter the following formula in the formula field: =WEEKDAY(B2,2)>5
Enter the formula for Conditional Formatting
  1. Click the Format button
Click the Format button
  1. Specify the formatting (I will go with the Yellow fill color)
Select the color for conditional formatting
  1. Click OK
  2. Click OK

The above steps would highlight those dates that are either a Saturday or a Sunday.

Dataset where weekends are highlighted

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:

 =WEEKDAY(B2,2)>6

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:

   =OR(WEEKDAY(B2,2)=2, WEEKDAY(B2,2)=4)

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster