If(and formula assistance

[dprobst]

I need assistance with the second part of am IF/AND formula. Normally I wouldn't get stumped but I am working with a date calculation and
trying to get it into the formula has me at a loss.

Here are the rules for the formula I am writing. I will include a sample spreadsheet as well.

RULE 1 - IF PLANNER DATE IS LESS THAN OR EQUAL TO TODAY'S DATE AND CONTRACT PAID AMT IS
LESS THAN OR EQUAL TO ZERO, THEN HALF CONTRACT TIME DIAPLAYS "CAUTION"
=IF(AND($B5<=$B$2,$C5<0),"CAUTION" (FIRST HALF OF FORMULA)

RULE 2 - IF THE CURRENT DATE IS WITHIN 5 DAYS OF THE DROP DEAD DATE, AND THE CONTRACT PAID
AMOUNT IS LESS THAN OR EQUAL TO ZERO, THE HALF CONTRACT TIME DISPLAYS "CRITICAL",
OTHERWISE THE HALF CONTRACT TIME DISPLAYS "OK",

[johnmpl]

Hi, dprobst!

You could try:
[A5] : =IF(C5<=0,IF(B5<=B$2,"CAUTION",IF(AND(E5-B$2>=0,E5-B$2<=5),"CRITICAL","OK")),"OK")

Blessings!

[63falcondude]

Try this in A5:

=IF(AND(E5-B$2<=5,C5<=0),"CRITICAL",IF(AND(B5<=B$2,C5<=0),"CAUTION","OK"))

[finalazy]

try this
=IF(AND($B5<=$B$2,$C5<=0),"CAUTION",IF(AND(E5-$B$2<=5,C5<=0),"CRITICAL","OK"))

[JohnTopley]

Try

=IF(AND($E5<=TODAY()+5,$C5<=0),"CRITICAL",IF(AND($B5<=$B$2,$C5<=0),"CAUTION","OK"))

[63falcondude]

Thanks for the rep and for marking this thread as SOLVED.