Identify if a date falls between one or more intervals in an array, and highlight cell so.

[clyng]

See attached spreadsheet.

My goal is to identify if the date in B9 falls between any of the intervals in B17:C23.

My first step was to work with an array function. It works nicely in D17:D23. But the same formula does not work in E17:E23.
Question 1: Can anyone explain me why D17:D23 produce the right result and E17:E23 does not?

My goal is to highlight B9, if the date I enter falls between any of the intervals in B17:C23. If I had succeded in understanding the mechanics to question 1, I was planning to use a similar function in 'Conditional Formatting'.
Question 2: What function should I put into the 'Conditional Formatting' for B9, so that the field is highlighted, if the date falls between one or more intervals in D17:C23?

[XOR LX]

Hi,

The reason is that you have committed the formula in column D as a multi-cell array formula (i.e. by first selecting the multi-cell range D17:D23 and then committing the formula with CTRL+SHIFT+ENTER), whereas you have committed the formula in column E as a single-cell array formula (i.e. by first selecting the single-cell range E17, then committing the formula with CTRL+SHIFT+ENTER, then copying down to cell E23).

A multi-cell array formula operates in a row-relative manner, meaning that each instance of the formula processes the row from the range passed according to its position within the multi-cell range to which the formula has been committed. So the result in cell D20 is evaluating the formula with respect to the entries in B20 and C20. Note that the first cell within the range for the multi-cell array formula does not have to be the same as the first cell containing the results: you could cut your present multi-cell array formula and paste it into any range you like, for example G21:G27, and it would still give the same results.

A single-cell array formula, such as the one you have here, will only ever process the first entry in the resulting array derived from your conditions, since that array is not being passed to any further indexing function.

Regards

[vba_php]

you can do it through the interface, like doing this multiple times:

[CARACALLA]

Conditional Formatting

=SUMPRODUCT(($B$9>=B17)*($B$9<=C17))>0


Applies to =$B$17:$C$23

[TMS]

Not sure why you have chosen to use an Array Function, other than you can. A simple:

Please Login or Register  to view this content.

copied down would give the same result. And I'd guess there's less of a performance hit. But, your choice.

For the Conditional Formatting, you could use the formula:

Please Login or Register  to view this content.

[clyng]

Hi,

The reason is that you have committed the formula in column D as a multi-cell array formula (i.e. by first selecting the multi-cell range D17:D23 and then committing the formula with CTRL+SHIFT+ENTER), whereas you have committed the formula in column E as a single-cell array formula (i.e. by first selecting the single-cell range E17, then committing the formula with CTRL+SHIFT+ENTER, then copying down to cell E23).

A multi-cell array formula operates in a row-relative manner, meaning that each instance of the formula processes the row from the range passed according to its position within the multi-cell range to which the formula has been committed. So the result in cell D20 is evaluating the formula with respect to the entries in B20 and C20. Note that the first cell within the range for the multi-cell array formula does not have to be the same as the first cell containing the results: you could cut your present multi-cell array formula and paste it into any range you like, for example G21:G27, and it would still give the same results.

A single-cell array formula, such as the one you have here, will only ever process the first entry in the resulting array derived from your conditions, since that array is not being passed to any further indexing function.

Regards

Thanks a lot for this answer to question 1. Now I understand. :-)

[clyng]

you can do it through the interface, like doing this multiple times:

Thanks. Yep. That works. I was hoping to put in a formula in just one rule and thereby avoid multiple rules.

Cheers

[clyng]

Conditional Formatting

=SUMPRODUCT(($B$9>=B17)*($B$9<=C17))>0


Applies to =$B$17:$C$23

Thanks. With this approach the date intervals will be highlighted. I am looking for a way to highlight B9 only.

Cheers.

[clyng]

Not sure why you have chosen to use an Array Function, other than you can. A simple:

Please Login or Register  to view this content.

copied down would give the same result. And I'd guess there's less of a performance hit. But, your choice.

For the Conditional Formatting, you could use the formula:

Please Login or Register  to view this content.

My bad. It was unclear. Column D and E are only there to illustrate how the same formula behaves differently. And now I understand why from the answer from XOR XL. Column D and E will not be there in the final version and I will not be able to search for the word 'MATCH!'.

Cheers