Date range

[jumpyjim]

Hi all


In the sample below are the cell references I want to include in the date range check. If possible in a single calculation, I want to know:

A) if any start/end date overlaps another from G9 through to P29.
B) a calculation, that were a date range exceeds today's date, highlight it and also highlight any other date range that falls into into the range that exceeds today's date.

So for example G24 and P24 would be the latest date range with G29 and P29 overlapping.


CELL START CELL DATE END


G9 01/01/2001 P9 01/01/2002
G14 01/02/2001 P14 01/04/2002
G19 01/03/2003 P19 01/04/2004
G24 01/04/2010 P24 01/06/2014
G29 01/09/2011 P29 01/01/2012


The formula can't specify a broad range, by stating G9:G29 for example would include hidden cells that contain data that I want to be excluded. I've tried =SUMPRODUCT(($G9<=$P$9+$P$14+$P$19+$P$24+$P$29)*($P9>=$G$9+$G$14+$G$19+$G$24+$G$29)>1 but with no success either. Perhaps I should be looking at another formula rather than SUMPRODUCT?

Your help is most appreciated.

[Bob Phillips]

See if this does what you want

=SUMPRODUCT(--($G$9:$G$29<>""),--($G9<=$P$9:$P$29),--($P9>=$G$9:$G$29))>1

[SamT]

Is it critical that you not highlight "G19" if it is earlier that "P18"?
IOW, what if a cell in one row is earlier then a date in a previous hidden row?

If not, try these conditional Format formulas:

In all Rows: If >= Date()
In all but the bottom row of dates: If >= $A10
In all but the top row of dates: If <= $P9

If you must exclude consideration of hidden cells, I think you will need a User Defined Function in VBA.

What is your ultimate goal with this process?

[jumpyjim]

I selected the 10 cells in question and applied conditional formatting with your formula and it is highlighting the cells, brilliant xld :)

I can almost see the finish line now with the only hurdle is that whilst it is indeed highlighting any overlapping cells, it does not appear to be taking today's date into account. So whilst any dates that overlap one another are highlighting through a simple fill colour via conditional formatting, it is not taking into account whether the date held in P column exceeds today's date and if so, to only highlight the date ranges that overlap it.

[Bob Phillips]

Is this what you mean?

=AND($G9<TODAY(),$P9<TODAY(),SUMPRODUCT(--($G$9:$G$29<>""),--($G9<=$P$9:$P$29),--($P9>=$G$9:$G$29))>1)

[jumpyjim]

If I use your new code through conditional formatting, it no longer works but I will revert to your original formula as it closely fits the desired output. Thanks again xld, very much appreciated :)

[Bob Phillips]

Maybe I have it back to front, maybe it should be > TODAY()?