Automated RAG status formulas using dates

Question

I have an excel spreadsheet where I would like the RAG status to autofill with the appropriate RAG colour appearing in column F. The RAG status would be based on whether a project is on time based on dates.

Column C = Planned completion date

Column D = Forecast/Actual completion date

Column F = RAG status

So for example

F1 would autofill RED if the date in D1 is 30 days later than the date in C1

F1 would autofill AMBER if the date in D1 is less than 30 days later than the date in C1 but more 14 days

F1 would autofill GREEN if the date in D1 is less than 14 days greater than the date in C1

I've tried to use conditional formatting but I can't work out the formula I need to reflect the relationship between the two dates.

Any help would be greatly appreciated!!!

Answer 1

Hi V_R_G_45

(Not sure how many times this issue/question has been raised on this site)

Re. your rules for Amber and Green, you wrote:

AMBER: ...but more 14 days

GREEN: ...less than 14 days greater than...

==> not quite sure how 14d should be colored. I opted for GREEN:

![](https://learn-attachment.microsoft.com/api/attachments/f2db5b87-2b6f-4e06-9726-3fbaa9a45298?platform=QnA"https://1drv.ms/x/s!AsuqEFbTJNgcgaFTZNt8oBgfHmVYwQ?e=rFV16B" title="https://1drv.ms/x/s!AsuqEFbTJNgcgaFTZNt8oBgfHmVYwQ?e=rFV16B" rel="ugc nofollow">here for download. Any problem to revise the rules for 14d let me know

Answer 2

Hi V_R_G_45!

I'm Jen, and I'd be happy to help you out with this issue.

Could you please try the formula below? Let me know if this is the result you're trying to achieve. Thanks.

Best Regards! Jen :)

Answer 3

Hi Jen,

That's exactly what I'm trying to achieve but...

I've tried to resolve it using your formula but it's tagging everything as amber, even dates projects that are coming in as less than 14 days off or projects have have finished early.

Answer 4

Hi V_R_G_45,

Can I see the formula in cell F6?

Thanks.

Answer 5

Hi Jen,

Here it is. I tried to see if doing the formulas for each row would resolve the problem but it hasn't