Explanation
Note: this is a great example of a formula that is hard to understand because the cell references are hard to interpret. The gist of the logic used is this: if the time in row 4 is between the start and end times, the formula should return TRUE and trigger the blue fill via conditional formatting. The actual implementation is a little more complicated because the formula below also takes into account the possibility that the start and end times cross midnight. If this is relevant to your situation, you can use just the AND expression explained below.
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 columns 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:
=IF($B5<$C5
When the start time is earlier than the end time, the test above returns TRUE, and IF returns the AND part of the formula:
AND(D$4>=$B5,D$4<=$C5)
The AND function is configured with two conditions. The first condition checks to see if the column time is greater than or equal to the start time:
D$4>=$B5
The second condition checks that the column time is less than or equal to the end time:
D$4<=$C5
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:
OR(D$4>=$B5,D$4<$C5)
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:
D$4>=$B5
The second condition is altered slightly to check if the column time is less than the end time:
D$4<$C5
When either condition returns 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.
Explanation
The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format “d”. With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in row 4 with the dates in columns B and C.
To shade days that are weekends, we are using a formula based on the weekday function . By default, the weekday function returns a number between 1 and 7 that corresponds to days of the week, where Sunday is 1 and Saturday is 7. However, by adding the optional second argument called “return type” with a value of 2, the numbering scheme changes so that Monday is 1 and Saturday and Sunday are 6 and 7, respectively.
As a result, to return TRUE for dates that are either Saturday or Sunday, we only need to test for numbers greater than 5. The conditional formatting formula applied to the calendar area (starting with D4) looks like this:
=WEEKDAY(D$4,2)>5
The reference to D4 is mixed , with the row locked so that the formula continues to evaluate the dates in the header for all rows in the calendar grid.
