Note references are mixed to lock rows and columns as needed in order to test each cell in the grid correctly. The calendar header (row 4) is a series of valid Excel times, formatted with the custom number format “hh”. This makes it possible to set up a conditional formatting rule that compares the time associated with each column in row 4 with the times entered in columns B and C. Each time in row 4 needs to be checked to see if it falls within the start and end times in column B and C, for each row of data in the schedule. The logic used to apply conditional formatting depends on the start and end times. When the start time is less than the end time (normal case) the AND function is used to trigger conditional formatting. When the start time is greater than the end time (times cross midnight) the OR function is used instead. To handle this distinction at a high level, the IF function is used first to check each pair of times: When the start time is earlier than the end time, the test above returns TRUE and IF returns the AND part of the formula: The AND function is configured with two conditions. The first conditions checks to see if the column time is greater than or equal to the start time: The second condition checks that the column time is less than or equal to the end time: When both conditions return TRUE, the formula returns TRUE, and triggers the blue fill for the cells in the calendar grid. When the start time is greater than the end time (times cross midnight), IF returns an expression constructed with OR: Here, the OR function is configured with two conditions. The first condition is the same as that used in AND above – it checks to see if the column time is greater than or equal to the start time: The second condition is altered slighly to check if the column time is less than the end time: When either condition return TRUE, OR returns TRUE, and triggers the conditional formatting. Note: both conditions use mixed references to ensure that the references update correctly as conditional formatting is applied to the grid.
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.
