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. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
