Google-sheets – Conditional Formatting with IF and THEN

conditional formattinggoogle sheets

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?

Best Answer

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.

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

Google-sheets – Conditional formatting with if formula

Because the formatting is Conditional you should not normally need an IF statement in the formula. Just:

=E88="yes"

should be sufficient, regardless of any text in D88.

Best Answer

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Google-sheets – Conditional formatting with if formula

Related Topic