• 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:

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

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

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
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 2020 Computergaga · All Rights Reserved ·