Conditional Formatting with Formulas

Originally Posted by Shazz

I am using Conditional Formatting with a Formula of =AND(B5-TODAY()>=0,B5-TODAY()<=5) this tell me if an item is due within 5 days and highlights the cell in red, I also want to be able to highlight the cell if it is overdue, does anyone now how I can do this?

If you mean that you want the same formatting for all these conditions, then you can just update your existing condition to:

=OR(AND(B5-TODAY()>=0,B5-TODAY()<=5),B5<TODAY())

Edit: Actually this is logically equivalent to:

=B5<=TODAY()+5

If you want the overdue payment to get a different format, Excel allows multiple conditions/formats in the same cell. Leave your original condition alone and add another condition

=B5<TODAY()

with corresponding formatting.

Also At the moment I have a formula in Cell D5 of =IF(ISNUMBER(F5),"Paid","Due") which changes to paid or due if an amount is entered in to F5, does anyone now how it change be done to have Paid, Due and Overdue in this formula?

The following formula does what you describe, but it seems that you also need to include the case where the payment is not yet due:

=IF(ISNUMBER(F5),"Paid",IF(B5<TODAY(),"Overdue","Due"))

This formula, and the one you started with, will both say Due even if the payment is not yet due.