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

Lookup and Reference Examples

  • How to reference named range different sheet in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Approximate match with multiple criteria in Excel
  • Perform case-sensitive Lookup in Excel
  • How to use Excel FORMULATEXT function

Data Analysis Examples

  • How to create dynamic reference table name in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Reverse List in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to Create Thermometer Chart in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation must begin with
  • Excel Data validation must contain specific text
  • Prevent invalid data entering in specific cells

Conditional formatting gantt chart in Excel

by

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

Formula

=AND(date>=start,date<=end)

Explanation

To build a Gantt chart, you can use Conditional Formatting with a formula based on the AND function. In the example shown, the formula applied to D5 is:

=AND(D$4>=$B5,D$4<=$C5)

How this formula works

The trick with 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 static date in D4, you can use =D4+1 to populate the calendar. This makes it easy to set up a conditional formatting rule that compares the date associated with each column with the dates in columns B and C.

Worked Example:   Excel Data validation only dates between

The formula is based on the AND function, configured with two conditions. The first conditions checks to see if the column date is greater than or equal to the start date:

D$4>=$B5

The second condition checks that the column date is less than or equal to the end date:

D$4<=$C5

When both conditions return true, the formula returns TRUE, triggering the blue fill for the cells in the calendar grid.

Worked Example:   How to use Excel AND Function

Note: both conditions use mixed references to ensure that the references update correctly as conditional formatting is applied to the calendar grid.

Post navigation

Previous Post:

Conditional formatting dates overlap in Excel

Next Post:

Conditional formatting gantt chart weekends 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 get page from URL in Excel
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into two parts in Excel Worksheet
  • How to find nth occurrence of character in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • How to get same date next year or previous year in Excel
  • Add workdays to date custom weekends in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • Get days, hours, and minutes between dates in Excel
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles