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 first cell in range in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • Offset in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Lookup and Reference Functions, References and Examples

Data Analysis Examples

  • How To Insert and Customize Sparklines in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to Sort by Color in Excel
  • How to count table rows in Excel
  • How To Remove Duplicates In Excel Column Or Row?

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation specific characters only
  • Excel Data validation allow weekday only
  • Excel Data validation whole percentage only
  • Excel Data validation exists in list

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.

Worked Example:   Highlight dates greater than in Excel

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.

Worked Example:   Conditional Formatting Data bars Examples in Excel

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.

Worked Example:   Get last weekday in month in Excel

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:

Conditional formatting gantt chart in Excel

Next Post:

Conditional formatting highlight target percentage 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
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • How to extract name from email address in Excel
  • Split dimensions into two parts in Excel Worksheet
  • How to convert numbers to text using TEXT function in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Count dates in current month in Excel
  • Get days, hours, and minutes between dates in Excel
  • Display Date is workday in Excel
  • Get fiscal quarter from date in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment periods for loan in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles