SOLVED

Excel auto fill gantt chart by 15 min interval

Copper Contributor

Hi,

 

I'm trying to make an auto-fill Gantt chart by 15 min intervals using conditional formatting. 

My current conditional formatting formula is =AND(H$1>=$C2,H$1<=$E2)

* H1 is 0000 (start of the 15 min intervals), C2 is Start time, E2 is End time

 

*0530 means the interval starting from 0530 to 0544

 

Eg. Start 0535 End 0725

I would like the box to highlight from AC9 to AK9, How do i do that?

Yanziizi_1-1653358984344.png

6 Replies

That formula seems correct assuming the RANGE the conditional formatting is applied to is $H$2:$CZ$2 (the formula must be correct for the top left corner of the Applied To Range)

If you want row 9 then replace the 2 with 9 or better yet if you want all the rows from 2 down to 9 then then just use that SAME formula and make the Applied To Range:

$H$2:$CZ$9  (notice again the top left corner is still H2 and so will work)

for example the start time is 0520

i will only highlight the cell for 0530 instead of from 0515
best response confirmed by Yanziizi (Copper Contributor)
Solution

AH you need the rounding to include those partials. I see. You just need to offset that start time
Try:
=AND(I$1>$C2,H$1<=$E2)

(but still applied to $H$2:$CZ$9 or whatever the end is)

it works thanks!
do you mind explain abit about the formula?
glad it works. The way conditional formatting works is that it starts in the very upper left cell of the 'Applied To' range and checks the formula. So in this case H2 will be True (i.e. special formatting applied) if the value in cell I1 is > the start time AND the value in H1 is <= the end time. Notice how there are $ in front of some of the letters/numbers and not others? When a $ precedes a letter or a number it means to 'lock' that letter or number while a letter or number without a preceding $ means it is relative.
SO when excel goes to the next cell I2 then the formula it will check is ... AND(J$1>$C2, I$1 <=$E2) basically the I and the H of the formula do NOT have a $ before them so when excel looks at the next column it also moves those cells to the next column also. Similarly when excel goes to the next row the number 2s will become number 3s but the number 1s have a $ in front so they will stay $1.
That all said, basically the formula LOCKS the reference to the 1st row but moves left and right accordingly and LOCKS the reference to the start and stop time columns but allows to move down to stay on the same row as the cell of interest.
Thank you very much for the explanation! It's very clear!
1 best response

Accepted Solutions
best response confirmed by Yanziizi (Copper Contributor)
Solution

AH you need the rounding to include those partials. I see. You just need to offset that start time
Try:
=AND(I$1>$C2,H$1<=$E2)

(but still applied to $H$2:$CZ$9 or whatever the end is)

View solution in original post