Help please if possible with nested IF statement I think

Hi All

I would like to produce a forumla to do the following:

If the date in Cell A2 is after today AND there is nothing in Cell B2 then add the words 'Out of Time' to Cell C2 and turn all the text in the line red. (tho I would settle for just the 'Out of Time' being red if whole line not possible - I spent ages playing with conditional formatting yesterday)

If the date in Cell A2 is after today AND the date in Cell B2 is before the date in Cell A2 then do nothing

If the date in Cell A2 is after today AND the date in Cell B2 is after the date in Cell A2 then add the words 'Out of Time' to Cell C2 and turn the line back to black text

If the date in Cell A2 is before today then do nothing

I think I need a nested IF forumla and thought I had done it yesterday, but I got in a tizz.

Any suggestions appreacited - thanks for taking the time

Try.

=IF(AND(A2>TODAY(),B2<A2),"",IF(AND(OR(A2>TODAY(),B2>A2),OR(A2>TODAY(),B2="")),"Out of Time",""))

Use the same conditions in conditional format rules for filling with colour the cell.

Hi Anne.

In your example that you gave to us in first post, formula works fine.

So for not trying without reason, pls upload a small sample workbook and manually type the expected results.

Why Q8 & Q9 must be "Out of Time"? By which condition?

Why Q11, must be nothing? What you mean by"2nd date is error"? The condition that you get a "Out of Time" is this.

.....P11>O11);OR(O11>TODAY();P11="")

Yes i must say that i am comfuse...

This formula works for Q8:Q12, but not for Q7. Which is the correct criterion for Q7?

=IF(AND(O8>TODAY(),P8<O8),"",IF(AND(O8>TODAY(),P8>O8),"","Out of Time"))

This one, gives the correct results.

=IF(AND(P7<O7,P7>0),"",IF(AND(O7>TODAY(),P7<O7),"",IF(AND(O7>TODAY(),P7>O7),"","Out of Time")))

You are welcome!

Thanks for the reb*