![]() ![]() Note: both conditions use mixed references to ensure that the references update correctly as conditional formatting is applied to the grid. When either condition return TRUE, OR returns TRUE, and triggers the conditional formatting. The second condition is altered slighly to check if the column time is less than the end time: D$4<$C5 For more information about the Gantt bar mark type, see Gantt Bar mark. The second condition checks that the column time is less than or equal to the end time: D$4=$B5,D$4=$B5 To handle this distinction at a high level, the IF function is used first to check each pair of times: =IF($B5=$B5,D$4=$B5 ![]() When the start time is greater than the end time (times cross midnight) the OR function is used instead. When the start time is less than the end time (normal case) the AND function is used to trigger conditional formatting. The logic used to apply conditional formatting depends on the start and end times. 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.Įach 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 calendar header (row 4) is a series of valid Excel times, formatted with the custom number format "hh". If this is relavant to your situation, you can use just the AND expression explained below. 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. 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. Note: this is a great example of a formula that is hard to understand because the cell references are hard to interpret. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |