Excel - Conditional formatting with multiple date ranges with one criteria

Question

I have a Excel sheet with two tables like the image bellow,

I need to do a conditional formatting to highlight the dates, on table A, that are inside the range of the table B, accordingly to the process number.

For example, for the process number 123456 I have two intervales in the table B, so the result should be,

I want to be able to do this for all the process numbers in the table A that have correspondence in table B.

In table B i can have multiple intervales for one process number, in this example this happens for the process number 123456.

excel conditional formating with multiple date ranges

With the post above I can retrive the data ranges from table B and apply the conditional formatting, but i need to insert the "Process number" criteria, otherwise excel applies the formatting to the entire column.

Can anyone help me?

Note: This is a simplified version of my tables, only to make easy to explain.

Thanks.

Answer 1

• Select area that should contain the conditional format. In my example start with B2 to make it the active cell.
• Enter =COUNTIFS($A$8:$A$10,$A2,$B$8:$B$10,"<="&B2,$C$8:$C$10,">="&B2)>0 into the formula field.

Explaining the formula

CountIfs count the number of occurances that match the following criteria. Highlight if there is any, in other words if the result of the formula is >0.

Explaining the params

• $A$8:$A$10 = Process numbers column (B list)
• $A2 = Process number in current row (A list)
• $B$8:$B$10 = Start date column (B list)
• B2 = reference to active cell; "<=" to check whether start date is smaller or equal current date
• $C$8:$C$10 = End date column (B list)
• B2 = reference to active cell; ">=" to check whether enddate is greater or equal current date