Conditional formatting cells based on 3 conditions

0
votes

I have a list of vehicles with start date and end date in sheet1 and a list of dates in sheet2. I need to highlight cells in sheet2 using conditional formatting.

The image is attached for reference.

This is what I actually mean:
Lookup value A11 in the array A2:C4, then conditional format cell range A12:A21 depending on the start date (b2:b4) and end date (c2:c4).

enter image description here

2
excelexcel-formula

2 Answers

0
votes

I would use Countifs in a case like this to see if the current date is within any of the given date ranges:

=COUNTIFS($A$2:$A$4,$A$11,$B$2:$B$4,"<="&A12,$C$2:$C$4,">="&A12)

enter image description here

The values in B12:B21 are just put in as a check and are not used.

0
votes

I would suggest to use two helper cells - label them start and end - and fill them using regular vlookup based on the input cell A11.

start E12:

=vlookup($A$11;$A$2:$C$4;2;FALSE)

end E13:

=vlookup($A$11;$A$2:$C$4;3;FALSE)

Then setup the formatting rules of the A12:A21 range based on the values of these two helper cells. In the wizzard that is the last option Use a formula to determine which cells to format.

The formula might look like this

=and(A12>=E12;A12<=E13)

EDIT: I haven't realized that the input value may repeat, sorry. In that case COUNTIFS or array formulas must be used.