Conditional Format - Column Date Range & Another Variable

[The Great SrH]

Hi,

I have an Excel Document which is a bit of risk project tracker. Due to the information I cant directly share it but will do a similar table below!

I would like to place a conditional format into the excel columns E-H (which i'm assuming would be a formula) based on a couple variables.

As an example, if a Status in Column B is On Track, I need the colour to be Green. If Status is Overdue, I need the colour to be Red.

I will need the Columns E-H to only show that colour if the columns are between the Start Date and End Date date range. I have written the words in columns E-H to show what the output I would expect to be.

Any ideas?!

A	B	C	D	E	F	G	H
Project	Status	Start Date	End Date	Mar-24	Apr-24	May-24	Jun-24
Project A	On Track	Mar-24	Jun-24	green	green	green	green
Project B	Overdue	Mar-24	Apr-24	red	red
Project C	On Track	Apr-24	Jun-24		green	green	green
Project D	On Track	Mar-24	May-24	green	green	green

 

[Joe4]

Select cells E2:H5, and then enter this Conditional Formatting formula:

=AND($B2="On Track",E$1>=$C2,E$1<=$D2)

and choose your "green" fill option.

Then, repeat those same steps, adding in another CF rule, this time with formula:

=AND($B2="Overdue",E$1>=$C2,E$1<=$D2)

and choose your "red" fill option.

Result:

 

[The Great SrH]

Select cells E2:H5, and then enter this Conditional Formatting formula:

=AND($B2="On Track",E$1>=$C2,E$1<=$D2)

and choose your "green" fill option.

Then, repeat those same steps, adding in another CF rule, this time with formula:

=AND($B2="Overdue",E$1>=$C2,E$1<=$D2)

and choose your "red" fill option.

Result:

View attachment 109321

Amazing - thank you so much for the quick response. My brain clearly isnt working today

 

[Joe4]

You are welcome.
Glad I was able to help!