There might be a better way, but try three conditional format rules on C1 in this order:
"Custom formula is"; Value:
=IF(A1="Done",TRUE,FALSE): set background to White"Date is after"; "exact date..."; Value:
=NOW()+3: set background to Green"Cell is not empty": set background to Red. This is the default if the cell is not "Done" and the date is not after three days from now.
You could also keep rule 1, and then have rule 2 being a Colour Scale. You might need to put an extra cell to convert NOW() into a day count, though: =NOW()-3 for example.
There might be a better way, but try three conditional format rules on C1 in this order:
"Custom formula is"; Value:
=IF(A1="Done",TRUE,FALSE): set background to White"Date is after"; "exact date..."; Value:
=NOW()+3: set background to Green"Cell is not empty": set background to Red. This is the default if the cell is not "Done" and the date is not after three days from now.
You could also keep rule 1, and then have rule 2 being a Colour Scale. You might need to put an extra cell to convert NOW() into a day count, though: =NOW()-3 for example.
One way to do it would be with a custom formula conditional format rule per colour. Instructions for adding a custom rule to conditional formula are available here. To summarize:
- Open a spreadsheet.
- Select the cell or range of cells that you want to apply formatting rules to.
- Click the Format menu.
- Select Conditional formatting.
- Under the “Condition” drop-down menu, choose Custom formula is.
- Add in the relevant formula and rules.
- Click Done.
You need to add the most restrictive rule first. Rules will be evaluated in order, so the first one that matches will determine the format to be applied. In your example, this means that you would need to add the red rule first followed by the green one.
In the example you gave, the range ("Apply to range" field) for all formulas would be C:C
The custom formula for the red rule would be:
=IF(EQ(A1, "Open"), IF(C1 <= TODAY() + 7, true, false), false).
This would apply the format to all dates which are within 7 days of today.
If you wanted to add an intermediate orange rule for dates within 28 days for example, the formula would be:
=IF(EQ(A1, "Open"), if(C1 <= TODAY() + 28, true, false), false).
The final green rule formula would be:
=IF(EQ(A1, "Open"), if(C1 > TODAY() + 28, true, false), false).
You may be able to simplify the number of rules or complexity of the formulae, but the above should work.
I keep track of morning stock, daily production and resulting EoD stock figures
I would like conditional formatting to highlight cells where the morning stock is over 10% of the previous days EoD stock, my current conditional formatting formula is =if((B2/D1)>=0.1,TRUE,FALSE).
Where column B is morning stock, Column C is production and column D is EoD stock.
It appears to be highlighting lots of cells but not following the formula logic, there's cells not highlighted that definitely fit the formula TRUE conditons (for example in E38 (2) and G37 (13), 2/13=0.153)
I want to use conditional formatting to take the cell column value and check another cell in that column and perform some basic arithmetic on it to determine the color of the cell, but I want to do this dynamically so I don't need to create a separate version for every column in the array. I know about INDIRECT("string") and COL functions but those don't seem to do it because I need a term that just reads in the formula as "this cell value".
Edit: In the example below I want a pair of conditional formatting rules for the rows "things 1-4" to be green if the referent/cell value is greater than 0.5 and red if it's less.