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
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 Startdate
(Column D) and the StartTime
(Column E). The result is a date value formatted as "Date/time"end:
=H4+I4
- being the Enddate
(Column H) and the EndTime
(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
- Cell L3 -
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