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

try this in cell G4

Formula:

Please Login or Register  to view this content.

and copy down and over

In cell BD3 your going to want to put a place holder date like 1/1/3000 to make sure the formula works in the last column. put the date in and format the text white to hide it.

Let us know if you need help with the conditional formatting.

[double posted]

Hi,

This is good thanks, I also have another question but will get back to you soon.

Cheers

So.. the next step I want is attached in my spreadsheet at the bottom half. Like last time the second part is hard coded and the top part formula.

Column D shows the baseline date, I need this now to have a fixed date in the spreadsheet and not move, black colouring and "B" text.
Column E the current baseline date should only be shown when the baseline date and current baseline date are different (column D & E).
Column F is a forecast date, this is a forecast date which we are forecasting completion. This should be in yellow colour and "F" text.

Any further help would be great, could imagine this getting quite complex now.

Many thanks