Formula that returns 1 if dates falls in date Range

[simplyc]

Hello All,
I am in need of a formula that returns "1" if the start date is before or on the active date and if the close date is before or equal to the active date. I am trying to determine how many project are active in that month. If the project is not active, I would like a 0 returned.
Here is an example of what the spreadsheet would look like:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column BM
Start Date[/TD]
[TD]Column BN
Close Date[/TD]
[TD]Column BP
Active
1/15/2015[/TD]
[TD]Column BQ
Active
2/15/2015[/TD]
[TD]Column BR
Active
3/15/2015

[/TD]
[/TR]
[TR]
[TD]1/26/2015[/TD]
[TD]3/1/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried the formula below and it did not work:
[TABLE="width: 71"]
<tbody>[TR]
[TD="class: xl63, width: 71"]=IF(OR(BM6<=BP4,BO6<=BP4),"1","0")[/TD]
[/TR]
</tbody>[/TABLE]

Any Help would be greatly appreciated !

 

[Rick Rothstein]

Hello All,
I am in need of a formula that returns "1" if the start date is before or on the active date and if the close date is before or equal to the active date. I am trying to determine how many project are active in that month. If the project is not active, I would like a 0 returned.
Here is an example of what the spreadsheet would look like:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column BM
Start Date[/TD]
[TD]Column BN
Close Date[/TD]
[TD]Column BP
Active
1/15/2015[/TD]
[TD]Column BQ
Active
2/15/2015[/TD]
[TD]Column BR
Active
3/15/2015

[/TD]
[/TR]
[TR]
[TD]1/26/2015[/TD]
[TD]3/1/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried the formula below and it did not work:
[TABLE="width: 71"]
<tbody>[TR]
[TD="class: xl63, width: 71"]=IF(OR(BM6<=BP4,BO6<=BP4),"1","0")[/TD]
[/TR]
</tbody>[/TABLE]

Any Help would be greatly appreciated !

First, I note you show BO6... your table shows Column BO not used, so I assume you meant BN6. See if this formula does what you want...

=IF(AND(BM6<=BP4,BP4<=BN6),1,0)

 

[bbott]

Maybe this in BP3, copied over and down:

=AND($BM3<=BP$2,$BN3>=BQ$2)+0

 

[simplyc]

Thank you both, for your fast responses!

These formulas are close to what I wanted. I used =IF(AND(BM5<=BO4,BN5<=BO4),1,0) and got zero in column BQ. I also tried =AND($BM5<=BO$4,$BN5<=BO$4)+0 and got zeros.
I would like to return a One for when the project is still active in that time frame. If the start date is before or on the active date and the close date is before or on the active date then I would like it to return a 1.
Like this :
What should I do differently to return these results?
[TABLE="width: 481"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_outer_border, width: 500"]
<tbody>[TR]
[TD]Column BM
Start Date[/TD]
[TD]Column BN
Close Date[/TD]
[TD]Column BP
Active
1/15/2015[/TD]
[TD]Column BQ
Active
2/15/2015[/TD]
[TD]Column BR
Active
3/15/2015
[/TD]
[/TR]
[TR]
[TD]1/26/2015[/TD]
[TD]3/1/2016[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you both, for your fast responses!

These formulas are close to what I wanted. I used =IF(AND(BM5<=BO4,BN5<=BO4),1,0) and got zero in column BQ. I also tried =AND($BM5<=BO$4,$BN5<=BO$4)+0 and got zeros.

Then I think your dates may be Text values instead of real dates. Assuming there is a "date" in cell BM5, what does this formula show...

=ISNUMBER(BM5)

 

[simplyc]

=ISNUMBER(BM5) returns TRUE

 

[bbott]

Thank you both, for your fast responses!

These formulas are close to what I wanted. I used =IF(AND(BM5<=BO4,BN5<=BO4),1,0) and got zero in column BQ. I also tried =AND($BM5<=BO$4,$BN5<=BO$4)+0 and got zeros.
I would like to return a One for when the project is still active in that time frame. If the start date is before or on the active date and the close date is before or on the active date then I would like it to return a 1.
Like this :
What should I do differently to return these results?
[TABLE="width: 481"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_outer_border, width: 500"]
<tbody>[TR]
[TD]Column BM
Start Date[/TD]
[TD]Column BN
Close Date[/TD]
[TD]Column BP
Active
1/15/2015[/TD]
[TD]Column BQ
Active
2/15/2015[/TD]
[TD]Column BR
Active
3/15/2015[/TD]
[/TR]
[TR]
[TD]1/26/2015[/TD]
[TD]3/1/2016[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/2/2013[/TD]
[TD]8/15/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

These are the results that I get with my formula. As Rick mentioned earlier, did you really skip column BO or was that a typo?

 

[Rick Rothstein]

=ISNUMBER(BM5) returns TRUE

Whichever cell in Column BP has your date in it, perform the same test using that cell's address.

 

[simplyc]

I did skip a column, it was not a typo. When I did the same test for BP it returned False

 

[simplyc]

I apologize it returned TRUE when I tested my date for BP