Exceljet

Quick, clean, and to the point

Gantt chart by week

Excel formula: Gantt chart by week
Generic formula 
=AND((date+6)>=start,date<=end)
Explanation 

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

=AND((D$5+6)>=$B6,D$5<=$C6)

When the expression above returns TRUE for a given cell in the calendar, the blue fill is applied.

Note: this is a variation on the by day Gantt chart example here.

How this formula works

In the example shown, row 5 is a header row and which contains a series of valid dates, formatted with the custom number format "d".  With a static date in D5, you can use =D5+7 (copied across) to populate the calendar header in row 5. 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.

The formula is based on the AND function, configured with two conditions. The first conditions checks to see if the date in the header row is greater than or equal to the start date + 6 days:

(D$5+6)>=$B6

The second condition checks if the date in the header is less than or equal to the end date in column C:

D$4<=$C5

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

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

Formula for month names

The month names in row 4 are generated automatically with this formula, copied across above the header in row 5:

=REPT(TEXT(D5,"mmm"),IFERROR(MONTH(D5)<>MONTH(C5),1))

This is an example of using the REPT function for a conditional message without the IF function. The MONTH function is used to compare months in the header row. When they are different, the month name is displayed.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.