If function and conditional formatting

[Resguy]

Hi,

I'm trying to set up a formula where by if the sent date was more than 5 days ago and we have not received anything back yet, column B turns red. If its less than 5 days since it was sent, Column B stays Green. If Column E has "Awaiting SAR", then column B turns orange.

The formula I have so far is:

=IF(E2=Sheet3!$F$3,2,IF(A2<=TODAY()-5,1,IF(B2="",0)))

Example sheet is attached.

The issue I'm having is when I put a date in Column B it should turn green, its not. Can anyone help?

Thanks,

Adam

[#2]

@Resguy Perhaps like this in C2 and copy down.

=IF(E2=Sheet3!$F$3,2,IF(AND(A2<=TODAY()-5,B2<>""),1,0))

[#3 · Accepted solution]

@Resguy 

Change the formula to

=IF(E2=Sheet3!$F$3,2,IF(AND(A2<=TODAY()-5,B2=""),1,0))

[#4]

This has worked, Thank you for your help.