IF statment to bring back RAG status on week end between multiple week-ends

Hi,

So I'm trying to create a visual plan which shows the RAG status of a reference number between two weeks.

The RAG of the reference number is either complete, red, amber or green. T

In my example:

HR-03. This currently has a green RAG and a baseline date of 30th september. 30th September is a Monday, and my reporting days are Fridays, therefore I want the formula to find the 30th between those two weeks and highlight the cell green with "G" text (as seen in cell V23).

However, if the milestone is complete (C) RAG, then I want it to look up the column complete date rather than baseline date, and do the exact same as above (as seen in G22).

In my attachment I have put in a hard copy, and a formula copy. The hard copy is what the formula copy should look like if the formula is correct.

I think this will only take a few IF statements all in one formula, so shouldn't be too difficult (unless you're me)!

Many Thanks