determining if any dates in a range, falling between two date.

[flashgordie]

Hello Again,

I was looking for some help to determine if any of the dates listed cells A6 through A17 is equal to (A1); or if they fall between A1 and A2). Placing a result in A3.

Example

A1: Mar 15-13
A2: Mar 29-13

A3: "Yes" (result or "no" depending on result)

A6: January-29-13
A7: February-26-13
A8: March-26-13
A9: April-26-13
A10: May-29-13
A11: June-26-13
A12: July-29-13
A13: August-28-13
A14: September-26-13
A15: October-29-13
A16: November-27-13
A17: December-19-13

I was thinking of vlookup, but could not get it to work out for that range. AS the dates are actually bill payments there will only be one date in a6:a17 that may fall withing the two week period of A1:A2

Any help would be appreciated. Cheers!

 

[Mark44stfc]

You could create another column to the right which you could then hide.

This would be and If statement

so IF(A6<A1,0,if(A6>A2,0,1))

You would then Simply put the following in A3

If(sum(B6:B17)>0,"Yes","No")

Prob a better way to do it but this makes it simple to track etc

 

[flashgordie]

Thanks Mark, I understand your thought process, but I was looking for "an all in one" solution. I could likely do it with and =if(or( condition as well but I thought there might be a better way with vlookup.

Thanks for your suggestion though!

 

[shg]

Assuming those are all Excel serial dates, then in A3

=COUNTIFS(A4:A15, ">=" & A1, A4:A15, "<=" & A2)

       ----A----
   1   15-Mar-13
   2   29-Mar-13
   3           1
   4   29-Jan-13
   5   26-Feb-13
   6   26-Mar-13
   7   26-Apr-13
   8   29-May-13
   9   26-Jun-13
  10   29-Jul-13
  11   28-Aug-13
  12   26-Sep-13
  13   29-Oct-13
  14   27-Nov-13
  15   19-Dec-13

 

[flashgordie]

Hi SHG, Thank you for your reply. This is closer and I will play with this a little more... I have altered it somewhat to include a statement ...

=IF(COUNTIFS(A4:A15, ">=" & A1, A5:A15, "<=" & A2)>0,"Payment has been deposited","No payment made"). As each date (in the list) will only occur once a month, I believe the countifs will yield the same result as I need.

One last question, I do not believe I have seen the way you structured your formular before (by using "&")..... is this the same as a =if(countifs(and( ?

Thanks!

 

[flashgordie]

Is there a way that if there is a date in the list a4:a15 that falls between a1 and a2...to get the formula this to return that date ....in the example to display "March 26-13" as opposed to the countifs that shows the number of occurances.

The countifs provides a usable result, but to manipulate it further, I've come to realize could really use the date if it falls between the range (A1 and A2)

Again, thanks for any help you may be able to provide.

 

[shg]

One last question, I do not believe I have seen the way you structured your formular before (by using "&")

The ampersand just catenates the comparison operator the the value that gets compared.

Is there a way that if there is a date in the list a4:a15 that falls between a1 and a2...to get the formula this to return that date ....

Something like (untested)

=iferror(index(a4:a15, match(1, (a4:a15>=a1) * (a4:a15<=a2), 0)), "no payment")

... which MUST be confirmed with Ctrl+Shift+Enter.

 

[flashgordie]

Hi Again, Sorry it has been so long, but I believe this will work too. Thanks for your help. It is greatly appreciated!!!