Using IF(AND with a few conditions

[Darren Carroll]

Hi I am looking to enhance the formula below.

I Have a "Due Date" in Cell B5 & a "Completed" Date in Cell C5. The Formula is in D5. The Results will allow conditional formatting for a RAG status.
RAG > 1=RED ,2=Green, 3=Amber

=IF(AND(C5="",B5<=TODAY()),1,IF(C5>B5,1,IF(C5<B5,2,3)))

I'd like to change the formula to give the same results as the above generates, but add in if the Date in B5 is between today and today()-5 it gives a result of 3... is this possible?

I have tried IF(AND(OR and various different fomulas but nothing works for me - which is why i am asking you for a little guidance please.

THanks in advance, Darren

 

[Mattster23]

Hi I am looking to enhance the formula below.

I Have a "Due Date" in Cell B5 & a "Completed" Date in Cell C5. The Formula is in D5. The Results will allow conditional formatting for a RAG status.
RAG > 1=RED ,2=Green, 3=Amber

=IF(AND(C5="",B5<=TODAY()),1,IF(C5>B5,1,IF(C5<b5,2,3)))

I'd like to change the formula to give the same results as the above generates, but add in if the Date in B5 is between today and today()-5 it gives a result of 3... is this possible?

I have tried IF(AND(OR and various different fomulas but nothing works for me - which is why i am asking you for a little guidance please.

THanks in advance, Darren

You need a nested if - something like this perhaps:
=IF(B5>=(TODAY()-5),3, IF(AND(C5="", B5<=TODAY()), 1, IF(C5<>"", 2)))</b5,2,3)))

 

[Darren Carroll]

Hi Mattster23,
Thanks for your response - with a little inspiration, and a bit or persiverance i came up with the enclosed and it works as i wanted.

=IF(L5>TODAY()+5,"",IF(OR(AND(L5>TODAY(),L5<TODAY()+5,M5=""),L5=M5,AND(L5=TODAY(),M5="")),3,IF(AND(M5<L5,M5<>""),2,IF(OR(M5>L5,AND(L5<TODAY(),M5="")),1,""))))

Regards, Darren