How to Highlight Weekend Dates in Excel?

How to Highlight Weekend Dates in Excel

When working with dates in Excel, you may need to highlight only certain days of the week on occasion.

You may, for example, have a list of dates on which you wish to emphasize all weekend dates.

With a little formula knowledge, Conditional Formatting can easily do this.

I’ll teach you how to highlight the weekend dates in a date data collection in this article. The technique described here may be used to emphasise any type of date (say Mondays or Tuesdays or alternate days)

So, let’s get started!

Use Conditional Formatting to Highlight Weekend Dates

In Excel, conditional formatting allows you to evaluate the value in a cell and then format it if the stated condition is fulfilled.

We can use Conditional Formatting to look at the date in a range of cells and highlight it if it falls on a weekend.

I’ve got a dates dataset below that I’d like to highlight all the dates that fall on a Saturday or Sunday.

Dates List Excel - Mygeekshub.com

The steps to accomplish so are as follows:

1. Choose the cells that have dates in them.

Select Dates Column

2. Click on the Home Tab

Click on home tab in Excel

3. Click the Conditional Formatting icon in the Styles group.

Conditional Formatting

4. Select the ‘New Rule’ option from the options that display.

New Rule

5. Select ‘Use a formula to determine which cells to format’ from the ‘New Formatting Rule’ dialogue box.

use formula to determine

6. Enter the following formula in the formula field: 

=WEEKDAY(B2,2)>5

Formula

7. Click the Format button

Click On Format

8. Specify the formatting (I will go with the orange fill color)

Select Highlighting Color

9. Click Ok

The above advances would feature those dates that are either a Saturday or a Sunday.

Abhijit

Abhijit currently working with India's No. 1 Ecommerce Company Flipkart. He having 6 years experience in Excel and he is also expertise in various Advance Excel formulas. He awarded by his organization for his outstanding contribution in Analytical Project.

This Post Has 3 Comments

  1. Rupjyoti Phukan

    Brilliant guide Abhijit…..

Leave a Reply