Forum Discussion
Solved
Excel auto fill gantt chart by 15 min interval
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 th...
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)
for example the start time is 0520
i will only highlight the cell for 0530 instead of from 0515
i will only highlight the cell for 0530 instead of from 0515
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!