Forum Discussion

Em_CD's avatar
Em_CD
Copper Contributor
Nov 22, 2023

Conditional Format +14 days from date and if cell in another column blank

Hi brains trust,    I am trying to set up a conditional format rule to highlight a row if it has been 14 days since the date in has passed, and if the corresponding cell in another column is blank ...
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Nov 23, 2023

Em_CD Start by selecting the entire data range that you want to be included in the rule (in the example shown below, my data range is from cell E2 to O11). Then, on the Ribbon, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and use the following formula:

 

=AND(ISBLANK($O2), TODAY()-$E2>14)

 

The formula only references the first row of the selected range but uses an absolute column + relative row reference for both $O2 and $E2. This is essential for highlighting the applicable row when the formula evaluates to TRUE. The results should look something like this:

 

Conditional Formatting Entire Rows

 

NOTE: if you also want to include rows where the entered Response Date is more than 14 days from the Letter Issue Date (ie: Row 3 shown in the above screenshot), use the following formula:

 

=IF(ISBLANK($O2), TODAY()-$E2>14, $O2-$E2>14)

Resources