Google-sheets – Advanced cell highlighting based on date in different cell and empty

conditional formattinggoogle sheets

I have a column set with dates in the future for different campaign events and another column with empty cells, but those cells will need information (text string) added to them within two weeks of the future date. How can I format the empty cells so that if today's date is two weeks out from the future date, and the cell is still empty, it highlights red?

Best Answer

This is achievable using the DATEDIF() formula. This will tell you how many days there are between two dates, though frustratingly it returns an error instead of a negative number if the first date is after the second.

First off you'll need your dates in a format that Google Sheets recognizes. You can see examples of known formats here. Then we'll be using the DATEVALUE() function to make that string into usable data.

I am assuming all dates are listed in column A, while the cells that require a text string are in column B. All instances of "A" and "B" will need to be changed accordingly.

DATEDIF(Today(), DATEVALUE($A1), "D")<=14

This will return a value of true for dates which will arrive in two weeks or less. The "D" at the end of this formula indicates that we want the difference in days, rather than months or years.

DATEDIF(DATEVALUE($A1), TODAY(), "D")<0

This will return a value of true for dates which are in the past.

Unfortunately these two rules are incompatible, since they will return errors, so you can't AND() them together. You will need to make two separate conditional formatting rules in order to catch both upcoming and past dates.

The other half of this is, of course, that you only wanted to flag cells which still don't have data entered for them. For this, we need the ISBLANK() function.

ISBLANK(B1)

This formula does exactly what it says on the tin. All that's left is to AND() it with the DATEDIF() rules, making your two full formulas as follows:

=AND(ISBLANK(B1),DATEDIF(Today(), DATEVALUE($A1), "D")<=14)

=AND(ISBLANK(B1),DATEDIF(DATEVALUE($A1), Today(), "D")>0)

Paste each of these in as formatting for column B, and you should be good to go.

Related Solutions

Advanced Date Logic in Google Sheets

Yes.

Use Conditional Formatting with three rules: (Format -> Conditional formatting)

"Date is before" "in the past week" -> red
"Date is after" in the past week" -> green
"Date is" "in the past week" -> orange

This will colour all dates more than a week away in green, all dates coming in the next week orange and the remainder of the dates in red. Empty cells will be left alone.

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.

Best Answer

Related Solutions

Advanced Date Logic in Google Sheets

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

Related Topic