Conditional Formatting with IF and THEN

Question

I would like to create a daily dashboard with cells that show the progress of my work regarding to a date. For example:

A1 is a text saying "Open". C1 is a cell that contains a date. This date changes the color depending on the amount of days in the future, e.g. 4 days in future = green, 2 days in future = red.

When the task is done and A1 says "Done", the C1 cell should now be white.

I imagine something like:
IF A1=Done THEN C1 Conditional Format is white background otherwise background is green or red depending on the days in the future.

Is this possible with Google Sheets?

Answer 1

There might be a better way, but try three conditional format rules on C1 in this order:

1. "Custom formula is"; Value: =IF(A1="Done",TRUE,FALSE): set background to White

2. "Date is after"; "exact date..."; Value: =NOW()+3: set background to Green

3. "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.

Answer 2

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:

1. Open a spreadsheet.
2. Select the cell or range of cells that you want to apply formatting rules to.
3. Click the Format menu.
4. Select Conditional formatting.
5. Under the “Condition” drop-down menu, choose Custom formula is.
6. Add in the relevant formula and rules.
7. 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.