Conditional Formatting

[jackb270193]

Hi There,

I am struggling to conditional format a cell to change colour based on comparing two dates against one another and against a time frame, for example:

Cell A1 Reads 27/01/2016
Cell A2 Reads 10/02/2016

I then want Cell A3 to change colour based on the below information:-

If Cell A2 is greater than Cell A1 by OVER 19 days then the cell will change Red
If Cell A2 is greater than Cell A1 by OVER 15 -18 days then the cell will go Amber
If Cell A2 is greater than Cell A1 by 15 or less days then the cell will go green

The above example, Cell A3 should go green because 10/02/2016 is less then 15 days older than 27/01/2016 if you understand.

I hope this makes sense.

Thanks!

 

[63falcondude]

=IF(A2-A1>19,"True")
=IF(AND(A2-A1>15,A2-A1<=19,"True")
=IF(A2-A1<=15,"True")

Try using these 3 formulas. This is not tested.

 

[FDibbins]

63falcondude when using CF, it only ever evaluates to TRUE or FALSE, so you just need to construct the rule based on that (don't need the IF)

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =A2-A1>19...format fill RED

Repeat 3 and 4 with...
=A2-A1>15 format fill Amber

and
=A2-A1<=15 format fill GREEN

If you enter them in that order/sequence, you wont need the AND part

Also, if you are OK with this, if you format the cell to green using regular formatting, you can do away with the last rule