Excel Conditional Formatting: Reference Column using NOW

Question

I'm trying to highlight rows created within the past 7 day period (since the last time checked). The table is tied to an external source that formats the column based on NOW (i.e., m/d/yyyy h:mm). So far, I found that conditional formatting is quite finicky when using functions.

First, I'll give the two formulas, which are working now. These are both based on a TODAY() format (i.e., mm/dd/yyyy)
1. =today()>$g2 --- this highlights past due items
2. =DATEDIF(TODAY(),$G2,"d")<30 --- this highlights items due within the next 30 days.

Now I need to reference another column (M), which is based on the NOW format mentioned above. I want the whole row formatted, so I can't use the built in functionality. I've used variations of the above, interchanging NOW() and TODAY(). Additionally, I cannot seem to get AND() to work in conditional formatting of an entire row. Any assistance would be greatly appreciated.

Answer 1

Select the entire sheet first, as conditional formatting only works on selected cells. Make sure all rows in your sheet are selected. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting > New rule.

In the New Formatting Rule dialog box, click "Use a formula to determine which cells to format".

Under Format values where this formula is true, type the formula: =$M1>TODAY() . (example provided as your required formula was not provided).

The formula uses the TODAY function to see if the dates in column M are greater than today (in the future). If so, the cells are formatted.

Click Format.

In the Color box, select your colour. Use other formatting commands as required. Click OK until the dialog boxes are closed.

The formatting is applied to column M when the condition is met.