jumpyjim
03-16-2014, 10:24 AM
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.
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.