Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Sort by Color in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to count table columns in Excel
  • Understanding Anova in Excel

References

  • How to get first column number in range in Excel
  • How to use Excel MATCH Function
  • How to use Excel FORMULATEXT function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Perform case-sensitive Lookup in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only
  • Excel Data validation no punctuation

Conditional formatting gantt chart weekends in Excel

by

This tutorial shows how to work Conditional formatting gantt chart weekends in Excel using the example below;

Formula

=WEEKDAY(date,2)>5

Explanation

To build a Gantt chart with weekends shaded, you can use Conditional Formatting with a formula based on the weekday function.In the example shown, the formula applied the calendar, starting at D4, is:

=WEEKDAY(D$4,2)>5

Note: this formula deals with weekend shading only. To see how to build the date bars with conditional formatting, see this article.

How this formula works

The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format “d”.  With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in row 4 with the dates in columns B and C.

To shade days that are weekends, we are using a formula based on the weekday function. By default, the weekday function returns a number between 1 and 7 that corresponds to days of the week, where Sunday is 1 and Saturday is 7. However, by adding the optional second argument called “return type” with a value of 2, the numbering scheme changes so that Monday is 1 and Saturday and Sunday are 6 and 7, respectively.

As a result, to return TRUE for dates that are either Saturday or Sunday, we only need to test for numbers greater than 5.  The conditional formatting formula applied to the calendar area (starting with D4) looks like this:

=WEEKDAY(D$4,2)>5

The reference to D4 is mixed, with the row locked so that the formula continues to evaluate the dates in the header for all rows in the calendar grid.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

Logical Functions

  • Nested IF function example in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function

Date Time

  • How to get workdays between dates in Excel
  • Convert time to time zone in Excel
  • Convert Unix time stamp to Excel date
  • Add days to date in Excel
  • Convert text to date in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel
  • Group numbers at uneven intervals in Excel
  • If cell contains one of many things in Excel

General

  • How to Insert Cells, Row and Rows in Excel
  • Advanced Number Formats in Excel
  • Hide and Unhide Columns or Rows in Excel
  • How to calculate percentage discount in Excel
  • How to generate random number weighted probability in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning