2

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?

2 Answers 2

4

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.

3
  • Thanks Gid, this almost works. The only thing I am stuck is with the fact that I want to request the content of each A cell from A1 to A500. The conditional format rules go from C1:C500, but the reference is always A1. It should be Ax for Cx. Is there a way to fix this?
    – sven
    Sep 4, 2015 at 11:34
  • Give it a go as is... while it might say A1, if you copy-and-paste C1 down to C2:C500, the rules should act on A2,A3 and so on even if when you click it it says A1. I've got a similar sheet myself. If you've messed with it, it might be confused; I noticed that too. Try clearing formatting, re-doing it from scratch and copy/pasting C1 downwards, and then leave it alone.
    – gid
    Sep 4, 2015 at 13:17
  • Cheers, this worked out! Got a really good document now :) Thanks a lot
    – sven
    Sep 4, 2015 at 13:58
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.