How do you utilize conditional formatting for times over multiple days in Google Sheets

Question

I am trying to visually see when events are taking place on our site. I have a list of events, their start date/time and end date/time. I would like to create a visual such as a Gantt chart that shows when the event is taking place. I have been successful in doing this for single day events by using the formula =AND(M$4>=$G5,M$4<=$J5), but if the event takes place over multiple days, I would like it to just highlight the times each day rather than the full time. For example if the event is taking place on 1/3/22 and 1/4/22 from 8am-10am each day I would like it to highlight those times on both days rather than from 8am on 1/3 to 10am on 1/4. Here is my spreadsheet. https://docs.google.com/spreadsheets/d/1lDqNSjs0rNi1ekVhsSdlDhsLWesaL0xOPHZqs9BwBM4/edit?usp=sharing

Answer 1

Your formula is "almost" right but there is a flaw in your date arithmetic. CONCAT does not yield date values (you can test this with isdate). This affects:

• values in Column F and Column I.
• all the values in Row 4

---

There are three values of significance:

• Start date/time
• End date/time
• Current date/time

Using the example below:

• start: =D4+E4 - being the Start date (Column D) and the Start Time (Column E). The result is a date value formatted as "Date/time"

• end: =H4+I4 - being the End date (Column H) and the End Time (Column I). The result is a date value formatted as "Date/time"

• Daily date/time (Row 3):

  • Cell L3 - =$L$1+L2, and copied to Cell Z3.
  • Cell AA3 - =$AA$1+L2, and copied to Cell AO3
  • Cell AP3 - =$AP$1+L2, copied to Cell BD3

• For the sake of transparency, I've left the cell colour formatting of time values in the range L2:Z2 so that they are visible. But this is not necessary and they could be hidden by applying a font colour equal to the fill colour.

• For the sake of clarity, I deleted row#4 from the sample spreadsheet. It was unnecessary.

Conditional formatting

• Select the range L4:BD10.
  • You could select a bigger (taller and wider) range if you have events that run into later days.
• Select "Custom formula is"
• Enter =AND(L$3>=$F4,L$3<=$J4)

---

Sample