Gantt chart by week

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.

Explanation

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 this formula in E5 (copied across) to populate the calendar header in row 5:

=D5+7 // header row

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.