I am working on a project management Gantt chart. There is conditional formatting set up for date range to be Red, Orange or Green depending on pending / completed activities within date range: =IF(AND(O$6>=$H7,O$6<=$I7,$M7="Pending"),"R",IF(AND(O$6>$I7,O$6<=$K7,$M7="Completed"),"A",IF(AND(O$6>=$H7,O$6<=$I7,$M7="Completed"),"G",""))).
Here O6 refers to day of the week, and H7 is planned start date, I7 is planned end date, K7 is actual end date. The original function was to correspond to each day. I have adjusted O6 date to be first Monday of the week (e.g. 15/04/2024, P6 is then 22/04/2024). The dates in planned start, end and actual end date are input by any date of the week. If the planned start date does not correspond to a specific Monday of the week, the function does not highlight the corresponding cells as the function does if the start date is the Monday of the week.
Is there any way to adjust the function to format cells if the planned start date e.g. H7 falls within the week commencing date within O6?
Thanks
Here O6 refers to day of the week, and H7 is planned start date, I7 is planned end date, K7 is actual end date. The original function was to correspond to each day. I have adjusted O6 date to be first Monday of the week (e.g. 15/04/2024, P6 is then 22/04/2024). The dates in planned start, end and actual end date are input by any date of the week. If the planned start date does not correspond to a specific Monday of the week, the function does not highlight the corresponding cells as the function does if the start date is the Monday of the week.
Is there any way to adjust the function to format cells if the planned start date e.g. H7 falls within the week commencing date within O6?
Thanks