Conditional formatting - number range to coincide with week commencing date

[RCarrigan]

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

 

[awoohaw]

welcome to the forum. It would be a big help to the forum if you could share a mini worksheet of your workbook using the xl2bb add in. If you can't use that then please copy and past a range of cells as a table. Pictures can be helpful but it means the form must completely recreate your scenario which is time consuming and can errors. The link the xl2bb add in is below.

Thanks in advance!