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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top