need a function (UDF) return X if 2 dates fall between 2 other dates

  • Thread starter Thread starter Chris Salcedo
  • Start date Start date
C

Chris Salcedo

what I need is to resolve the following:

Start Date End Date 11/1/09 11/8/09 11/15/09
11/22/09 11/29/09 12/6/09
11/3/09 12/30/09 X
X X X X
11/9/09 12/07/09
X X X X X
11/10/09 12/25/09
X X X

This is kind of a poor mans gant chart

Any help would be appreciated....
 
Hi Chris,

put the following formula in cell C2:

=IF(OR(AND($A2>=C$1,$A2<D$1),AND($B2>=C$1,$B2<D$1),AND($A2<C$1,$B2>C$1)),"X","")

Copy the formula for the rest of the cells.
A B C D E
1 Start Date End Date 11/1/2009 11/8/2009 11/15/2009
2 11/3/09 12/30/09 X X X
3 11/9/09 12/7/09 X X
4 11/10/09 12/25/09 X X
 
Hi Chris,

put the following formula in cell C2:

=IF(OR(AND($A2>=C$1,$A2<D$1),AND($B2>=C$1,$B2<D$1),AND($A2<C$1,$B2>C$1)),"X","")

Copy the formula for the rest of the cells.
        A       B               C      D       E
1       Start Date      End Date                11/1/2009       11/8/2009       11/15/2009
2       11/3/09 12/30/09                X       X       X
3       11/9/09 12/7/09                 X       X
4       11/10/09        12/25/09                        X       X
Click to expand...

Thanks Fred and Peggy,
Fred yours did not quite work correctly but Peggy yours worked
perfectly...

Thanks both of you for your help ......

Chris
 
Hi Chris,

You are welcome. Would you mind indicating that this was helpful by clicking
Yes?

Thanks,

Peggy Shepard
 
Back
Top