# Advanced cell highlighting based on date in different cell and empty

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?

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.