MS Excel VBA

MS Excel VBA
Day Name from Date in MS Excel

How to Highlight Weekend Dates in Excel?

35 Comments

How to Highlight Weekend Dates in Excel?

 

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

For a sample, you may have a list of dates where you want to highlight all the weekend dates.

It can quickly be done using Conditional Formatting with a bit of formula know-how.

In this tutorial, MS Excel VBA 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 dates (say Mondays or Tuesdays or alternate days)

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 requirement is met.

We can use Conditional Formatting to explore the data in a range of cells, and if the date lies on the weekend, highlight it.

Below I contain 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 Of 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

5 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 The 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

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.

If the result of that formula is TRUE, then the cell is highlighted in the specified color (yellow in this example), and if the development of the formula is FALSE, then nothing happens.

I had used the WEEKDAY formula that takes the date as the information 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 one, and if it is a Tuesday, it would return 2, and so on. For Saturday and Sunday, it returns at 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 five or not. I would only return TRUE for those dates on a Saturday or Sunday.
So, only those dates that occur on weekends (Sat or Sun) exist highlighted.
The exact 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 Mention formula in Conditional Formatting to only highlight dates that occur on Sunday:

 =WEEKDAY(B2,2)>6

Highlight Specific Days of the Week

If 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 two is for Tuesday and four 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 helpful tutorial!

Learn Complete Microsoft Excel Tutorial

Related Posts

35 thoughts on “How to Highlight Weekend Dates in Excel?”

  1. erotik

    I all the time used to read paragraph in news papers but now as I am a user of net thus from now I am using net for articles, thanks to web. Micah Saintamand

    Reply
  13. bahis

    Useful info. Lucky me I discovered your site by chance, and I am stunned why this coincidence did not came about in advance! I bookmarked it. Ian Carrell

    Reply
  28. passwords

    Ahaa, its fastidious discussion concerning this article at this place at this webpage, I have read all that, so now me also commenting at this place. Antonia Christmas

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top