CODEWITHSUNDEEP
exceldateformattingconditional-statementsrange
nioxys
nioxys

Reputation: 29

Excel - Conditional formatting with multiple date ranges with one criteria

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

enter image description here

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,

enter image description here

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.

Upvotes: 1

Views: 568

Answers (1)

Qualia Communications
Qualia Communications

Reputation: 715

enter image description here

  • 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

Upvotes: 1

Related Questions