Conditional formatting rule based on multiple criteria and today's date

Question

I have a large client list with multiple columns. Columns of interest:

column E - file status/needs,
column H - next appointment date.

I need a formula to change the formatting of the row if column E reads "executed" or "signed" only if column H also reads a date prior to today's date.

For example today is aug 11, 2016, joe schmoes file is "signed" (column E) and the next appointment date is "aug 9, 2016" (a past date in column H).

I need the row to be highlighted so we can see that this file needs attention.

I have tried many different formulas and I am not getting anything to work exactly correct. The closest formulas I have are below:

=IF(AND(TODAY()-$H1>=0,TODAY()-$H1<=15),SEARCH($E1="signed",$E1="executed"))

The problem is if the cell in column E reads" signed" like I need it to the formatting doesn't apply - formatting only applies when column E returns a false value and the words signed or executed are not in that row's E cell.

I know the first part is also not absolutely correct as it is only searching for a value in column H between today and 15 days past. (I couldn't figure out how to write the formula to be ANY past date).

Another one I've tried that has worked is:

=IF(AND(TODAY()-$H1>=90,TODAY()-$H1<=365),SEARCH($E1="signed",$E1="executed"))

Obviously this is only for values in column H between 90 and 365 days past today but I'm having the same thing where the formatting doesn't apply if column E reads either of the two searches entered.

I have also thought of doing negative rules, basically write a rule that any past date in column H that has a word other than "signed" or "Executed" in column E will return a certain format but haven't tried any formulas for this yet.

If the formula you have is totally different than what I have but accomplishes the goal that is fine I just need this to work and I'm spending a ton of time using the research, trial and error method.

Answer 1

Select your entire sheet and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(IFERROR(SEARCH("executed",$E1)>0,SEARCH("signed",$E1)>0),$H1<TODAY())

Format..., select colour Fill (highlight) of your choice, OK, OK.

This assumes (amongst other things!):

• executed and signed may be only part of a cell's content, might occur together and should trigger CF regardless of case.
• if the next appointment date is actually today, no fill.
• the dates are not text format.
• your version of Excel has IFERROR.

=AND handles the requirement that different columns are involved in the trigger.

Individually these are covered by a long element and, for the short element, the =TODAY function where the less than (<) operator if for where the date value in Column H is any date less than the current date, or no date value at all.

The longer element is a pair of =SEARCH functions, one each for executed and signed so that either (or both) will contribute to the CF trigger. Since the data may not be required to be case sensitive SEARCH was preferred to =FIND.

SEARCH returns the index of the position at which the search term is found and an error if not found. Since for these purposes where in the cell does not matter, any numeric result would serve and >0 covers all those possibilities. =IFERROR was used to trap an error arising from the absence of executed to allow searching to proceed for signed in that situation (otherwise the overall result of the formula would be an error and the CF not be triggered).