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

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

F

#### Fred Smith

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

Regards,
Fred

P

#### 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

C

#### 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

P

#### pshepard

Hi Chris,

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

Thanks,

Peggy Shepard