Flagging columns in Excel using date

[JulesMary72]

Hi Excel community,

I am having an issue with a spreadsheet I am creating and am hoping someone can help!  I have attached an example of what my spreadsheet looks like.  I need the date in each quarter to turn orange two weeks before the date, red two weeks after the date and if I enter a "C" in the column (for complete) then the cell turns green - this is the only part I have managed to master!

Thanks in advance! 

[#2 · Accepted solution]

@JulesMary72 A set of three custom conditional formatting rules can do this.  I didn't totally follow where you were putting the "C" but I have made a mockup in the attached.

[#3]

@Savia Thank you so much for the response, I really appreciate it.  Apologies for the delay in answering but the world has gone mad since I asked the question!  I will give your suggestions a go this week :)