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

[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....

 

[Fred Smith]

Try:
=IF(AND($C1>=A$2,$C1<=B$2),"X","")

Regards,
Fred

 

[pshepard]

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

 

[Chris Salcedo]

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

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

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

Chris

 

[pshepard]

Hi Chris,

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

Thanks,

Peggy Shepard