Conditional formatting - number range to coincide with week commencing date

RCarrigan

New Member
Joined
May 15, 2024
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top