May 23 2022 07:28 PM - edited May 23 2022 07:32 PM
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?
May 23 2022 07:38 PM - edited May 23 2022 07:43 PM
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)
May 23 2022 07:41 PM
May 23 2022 07:49 PM - edited May 23 2022 07:51 PM
SolutionAH 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)
May 23 2022 07:53 PM
May 23 2022 08:08 PM
May 23 2022 09:31 PM
May 23 2022 07:49 PM - edited May 23 2022 07:51 PM
SolutionAH 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)