Between Function

[94Sport5sp]

Hi:

I would like to do a test for values between 2 dates in Excel 2003. Since I did not find a between function I am looking for a different way to test this.

Example if range of date is May 1, 2015 up to and including May 20, 2015 I would like to find any Start date which begins on or after May 1, 2015 but ignore and Start date after May 20, 2015. Also any records which satisfy the above start date with any End date greater than or equal to May 1, 2015.

How can i do this in Excel?

Thanks

 

[Gasman]

I would just write my own?

Public Function BetweenDate(SelDate As Date, minDate As Date, maxDate As Date) As Boolean

If SelDate >= minDate Then
    If SelDate <= maxDate Then
        BetweenDate = True
    Else
        BetweenDate = False
    End If
Else
    BetweenDate = False
End If

End Function

and in the cells

=betweenDate(A1,"01/5/2015","20/5/2015")

 

[Brianwarnock]

You can do it in the worksheet using
If(AND(logicaltest1,logicaltest2).... Functions

Brian

 

[94Sport5sp]

Both examples will work when applied to a single row of data. When different combinations are added then problems start to arise. I believe I need to add more sample to describe my problem.

Assume inclusive report range starting May 2, 2015 and ending May 16, 2015. The question I would like Excel to answer for the date beginning with Start up to and including End, is there a date that matches the report inclusive period of May 2, 2015 to May 16, 2015?

In my example, True means there is at least one date that matches the report period Client, and False means that there is no dates withing the report period for this Client.

Sample data and results is:


Client Start End Date In Range
116 27-Apr-15 29-Apr-15 False False
118 27-Apr-15 May 1,2015 False False
110 2-May-15 15-May-15 True True
116 15-May-15 20-May-15 True True
108 19-May-15 20-May-15 False False
102 1-May-15 26-May-15 True True
121 7-May-15 14-May-15 True True
116 15-Jun-15 27-Jun-15 False False

 

[Brianwarnock]

Is What you are trying to do is the following but in Excel ?

http://www.baldyweb.com/OverLap.htm

I think it should be possible but haven't time to test today.

Brian

 

[Gasman]

Client 102 should be False False.?

If you apply the function/formula to both dates, you should be able to filter on either column being True.?

Both examples will work when applied to a single row of data. When different combinations are added then problems start to arise. I believe I need to add more sample to describe my problem.

Assume inclusive report range starting May 2, 2015 and ending May 16, 2015. The question I would like Excel to answer for the date beginning with Start up to and including End, is there a date that matches the report inclusive period of May 2, 2015 to May 16, 2015?

In my example, True means there is at least one date that matches the report period Client, and False means that there is no dates withing the report period for this Client.

Sample data and results is:


Client Start End Date In Range
116 27-Apr-15 29-Apr-15 False False
118 27-Apr-15 May 1,2015 False False
110 2-May-15 15-May-15 True True
116 15-May-15 20-May-15 True True
108 19-May-15 20-May-15 False False
102 1-May-15 26-May-15 True True
121 7-May-15 14-May-15 True True
116 15-Jun-15 27-Jun-15 False False

 

[94Sport5sp]

Hi:

I finally got it working. At least all my tests produce the results that I want.

Brian:

Yes that link does describe what I am trying to do, and my solution is very close to what that site posted. I ended up using an if statement with 2 imbedded if statements. I check if the end of the client range is before the beginning of the report. If true discard, if not test if the beginning of the client range is after the end of the report. If true discard, otherwise determine if to use report date or client date.

As the site says the logic seems counter intuitive but it works.

Gasman:
Because 102 client range has dates withing the report period it is true even though the begin date precedes the report and the end exceeds the report period. It has dates withing the report period.

Thanks for listening, I need someone to explain this to so I could understand what I was trying to do.

 

[Brianwarnock]

The If(AND logic would be

IF(AND(clientstart<=rangeend,clientend>=rangestart),True,False)

Brian

 

[94Sport5sp]

Hi Brian:

Yes, that would eliminate one of the nested If to generate the correct results.