• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Highlight Saturday and Sunday in a List

Highlight Saturday and Sunday in a List

The Conditional Formatting tool can be used for some very cool tricks in Excel. Today, I needed it to format all the Saturdays and Sundays in a list.

A user had a list of dates and she wanted to highlight each Saturday and Sunday in the list so that they would be instantly recognisable. It also creates and nice effect by splitting each week into blocks.

Create the Conditional Formatting Rule

  1. Select the cells containing the dates
  2. Click the Conditional Formatting button on the Home tab
  3. Click New Rule in the list
  4. Select Use a formula to determine which cells to format from the top half of the window
  5. In the Format values where this formula is true box, enter the formula below
=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)
Formula with WEEKEND function in a Conditional Formatting rule
  1. Click the Format button and select the formatting you want to use to identify the weekend days
  2. Click Ok
Highlighted weekend dates in Excel

Understanding the Formula

The OR function is used to test the two conditions and evaluate the answer to true or false. The two conditions are:

  • Is this date a Saturday – WEEKDAY(A2,2)=6
  • Is this date a Sunday – WEEKDAY(A2,2)=7

The WEEKDAY function is used to return the number that represents the day of the week for a date. The Weekday function requires two items of information.

Serial Number – The date you want to return the day of the week for. Cell A2 was used in the formula above

Return Type – A number that determines what day of the week a week begins. Number 2 was used in the formula above to determine that a week begins on a Monday and ends on a Sunday. Therefore Saturday is the sixth day and Sunday is the seventh of the week.

Related Posts:

  • Compare Two Lists in Excel to Highlight Matched Items
    Compare Two Lists in Excel to Highlight Matched Items
  • Shrinking Drop-Down List using Dynamic Array Formulas
    Shrinking Drop-Down List using Dynamic Array Formulas
  • Sort by Drop Down List in Excel
    Sort by Drop Down List in Excel

Reader Interactions

Comments

  1. mohideen says

    24 August 2016 at 8:31 pm

    that is great, I did not know this could be achieved, it is very useful.

    Reply
  2. Kanhaiyalal Newaskar. says

    25 September 2020 at 8:33 am

    Sir, 25th Sept.2020.
    Very clearly you have shown the topic,which is very easy to understand.
    Hoping to receive more ideas in near future too.
    Thanking you very much for taking efforts.
    Kanhaiyalal Newaskar.

    Reply

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • LIVE Currency Exchange Rate in Excel
  • N Function in Excel
  • Non-Adjacent Columns with FILTER Function
  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·