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


1 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)



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.