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:
The second condition checks if the date in the header is less than or equal to the end date in column C:
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:
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.