Hello everybody,
I am trying to make a gantt chart using conditional formatting. The problem is that I sometimes have several time intervals within one day and I don't know if there's a function that can achieve this.
Please see the preview in the attachment for easy understanding.
A1:F11 is the data the chart is based on (columns B,C,E,F are additional and are created using functions LEFT and RIGHT - if it could be done without them, then that would be even better)
Rows 14 to 19 is what I am trying to achieve (I changed the colour manually here)
"Format values where this formula is true" = Here I am looking for a function that will check the date in the chart table (A14:A19), then search for the same dates in B4:B11 and return true if it finds one where the start time (in column C) in the same row is smaller or equal to the time in the heading of the chart (row 13) and the end time (in column F) is greater than this time.
If there was just one time interval in one day and the dates above the chart were in ascending order, it would be easy and the "Format values where this formula is true" for example for the first row of this chart (B4:Y14) could be set as =AND(B$13>=$C11,B$13<$F11).
Any idea how to achieve this when there are sometimes multiple time intervals within one day as shown in the preview?
Thank you very much for your help.
Bookmarks