Conditional Formating

[Mad Hatter49]

I am trying to use conditional formating to change the colour of cells in Excel 2003 on WinXP for the following data:Due Date(A1) - Delivered Date(B1) - Days Overdue(C1)In (C1) I am using the formula =IF(ISBLANK(B1),"",A1-B1)This returns either a positive number if delivered before due date, negative if after and blank if not yet deliveredI am trying to turn C1 Green if delivered on time, Red if late or overdue and no change if undelivered and not yet dueIam using the following conditional formats:Condition 1 - Cell value is - greater than or equal to - 0 - Format=GreenCondition 2 - Cell value is - less than - 0 - Format=RedCondition 3 - Formula is - ="IF(A1<TODAY())" - Format=RedCondition 1 & 2 are both working, however if B1 is blank C1 is turning Green, the desired result would be no change if not yet due and Red if overdueCan anyone please help me with this?

 

[Momentman]

Do you encounter any errors or problems while doing this?

 

[Mad Hatter49]

No errors, just the undesired formatting results mentioned above.PS apologies for the formatting in my opening post, my carriage returns seem to have been removed!

 

[Mad Hatter49]

Sorry I had problems with the posting, I have now altered my forum settings and it now seems to be working.
The following is what my opening post should have looked like:

I am trying to use conditional formatting to change the colour of cells in Excel 2003 on WinXP for the following data:

Due Date(A1) - Delivered Date(B1) - Days Overdue(C1)

In (C1) I am using the formula =IF(ISBLANK(B1),"",A1-B1)
This returns either a positive number if delivered before due date, negative if after and blank if not yet delivered.

I am trying to turn C1 Green if delivered on time, Red if late or overdue and no change if undelivered and not yet due.

I am using the following conditional formats:
Condition 1 - Cell value is - greater than or equal to - 0 - Format=Green
Condition 2 - Cell value is - less than - 0 - Format=Red
Condition 3 - Formula is - ="IF(A1 < TODAY())" - Format=Red

Condition 1 & 2 are both working, however if B1 is blank C1 is turning Green, the desired result would be no change if not yet due and Red if overdue.

Can anyone please help me with this?

 

[Momentman]

For condition 1, use =AND(ISBLANK(B1)=FALSE,C1>=0)
For condition 2, use =AND(ISBLANK(B1)=FALSE,C1<0)

If you dont have the isblank in your conditional formula, the cell would change to green even if B1 is blank

 

[Mad Hatter49]

Many thanks for that Momentman, that has solved half (and the main part) of my problem and I think I almost understand how it works , a little bit of reading needed I think.

The bit that has not been resolved is turning C1 red if the delivery is overdue, I suspect that I may be able to solve that myself once i fully understand what you did, probably using the TODAY() comand somewhere.

 

[Momentman]

Many thanks for that Momentman, that has solved half (and the main part) of my problem and I think I almost understand how it works , a little bit of reading needed I think.

The bit that has not been resolved is turning C1 red if the delivery is overdue, I suspect that I may be able to solve that myself once i fully understand what you did, probably using the TODAY() comand somewhere.

If the delivery is overdue, isn't that already taken care of by condition 2, when C1 is negative?

 

[Mad Hatter49]

C1 will not be negative due to the formula that I'm using in the cell "=IF(ISBLANK(B1),"",A1-B1)"

However you have got me thinking and I've now changed that formula to "=IF(ISBLANK(B1),A1-TODAY(),A1-B1)" which now provides a figure for how many days overdue. I have changed your 2nd condition to "=C1 < 0" and now it works perfect .

Many thanks for your help, not only does my spreadsheet now work but probably even better I now understand the AND function

 

[Momentman]

Thats good to know. Cheers