Conditional Formatting

[Stuart_Mansfield]

What am I doing wrong?

I want the cells to change colour to show when training dates are due on a spreadsheet. I have decided to use conditional formatting as I thought this would be easier, but for some reason it won't change colour as I change the dates.

I want the cells to stay green if the date is over 31 days away from being due. I then want it to change to orange if the due date is within 31 days and red if it is due in 7 days.

Help............

[DonkeyOte]

You need to give the correct precedence to the various rules... think of it like:

=IF(4>31,"Green",IF(4<31,"Amber","Red"))

it would follow that "Amber" would be returned (rather than Red as hoped) given this is the first TRUE (an IF will exit upon TRUE), whereas:

=IF(4>31,"Green",IF(4<=7,"Red","Amber"))

would return "Red" ...

[Stuart_Mansfield]

Where do I type this in as its not conditional formatting is it?

If this is in the functions section I'm not hot on these and don't really know how to use these.

[DonkeyOte]

You don't need to type anything in - I was trying to use the IFs to demonstrate the IF logic.

You must set your Rules up such that the Red test is conducted before the Amber test given the conditions are not mutually exclusive (ie a value that holds true for Red is true for Amber test also)

If you prefer to not do that you must amend the Amber test such that it becomes an AND test, ie:

=AND(B4-TODAY()>7,B4-TODAY()<=31)

this way should the difference be <=7 the above will return FALSE rather than TRUE as is presently the case - and as such the subsequent Red test will be evaluated.