Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of named range in Excel
  • How to create dynamic named range with OFFSET in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to use Excel ROW Function
  • How to get last row in mixed data with blanks in Excel

Data Analysis Examples

  • Create Scatter Chart in Excel
  • Get column index in Excel Table
  • How To Sort One Column or Multiple Columns in Excel
  • How to perform a t-Test in Excel
  • Chart Axes in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation no punctuation
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Excel Data validation allow weekday only

Highlight dates that are weekends in Excel

by

This tutorial shows how to Highlight dates that are weekends in Excel  using the example below;

Formula

=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)

Explanation

If you want to use conditional formatting to highlight dates occur on weekends (i.e. Saturday or Sunday),  you can use a simple formula based on the WEEKDAY function.

For example, if you have dates in the range C4:C10, and want to weekend dates, select the range C4:C10 and create a new conditional formatting rule that uses this formula:

=OR(WEEKDAY(C4)=7,WEEKDAY(C4)=1)

Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be C5 in this case.

Worked Example:   Highlight duplicate columns in Excel

Once you save the rule, you’ll see all dates that are a Saturday or a Sunday highlighted by your rule.

Worked Example:   Highlight rows that contain in Excel

How this formula works

This formula uses the WEEKDAY function to test dates for either a Saturday or Sunday. When given a date, WEEKDAY returns a number 1-7, for each day of the week. In it’s standard configuration, Saturday = 7 and Sunday = 1. By using the OR function, use WEEKDAY to test for either 1 or 7. If either is true, the formula will return TRUE and trigger the conditional formatting.

Worked Example:   Highlight entire rows in Excel

Highlighting the entire row

If you want to highlight the entire row, apply the conditional formatting rule to all columns in the table and lock the date column:

=OR(WEEKDAY($C4)=7,WEEKDAY($C4)=1)

Post navigation

Previous Post:

Highlight duplicate columns in Excel

Next Post:

Highlight dates in the next N days in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count specific words in a cell in Excel
  • How to strip non-numeric characters in Excel
  • How to extract domain name from URL in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to Separate Text Strings in Excel
  • Display Date is same month in Excel
  • Next biweekly payday from date in Excel
  • Get month from date in Excel
  • Get age from birthday in Excel
  • Count day of week between dates in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • DDB function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles