Function BetweenDates

[Pinaceous]

Hi All,

I'm working on a macro whereby I'd like it to function between two dates, respectfully.

And pop an Msgbox, if it falls outside of these two dates.

startDate = "15/01/2015"
endDate = "25/03/2015"

I've picked these dates as a test.

I have this posted at the top before my code:

Function BetweenDates(startDate As String, endDate As String) As Boolean
    BetweenDates = IIf(CDate(startDate) <= CDate(endDate), True, False)
End Function

Dim startDate As String, endDate As String
startDate = "15/01/2015"
endDate = "25/03/2015"
On Error GoTo ErrHandler1



'Code stuff
            

         
ErrHandler1:
        MsgBox "This was already Executed!!"

Application.Cursor = xlDefault  'on completion / error

 
    Exit Sub
    
End Sub

It worked once, than it didn't after that, if you can believe it.

Can you please help me sort this out properly?

Many thanks in advance!
Pinaceous

 

[Scott Huish]

Your comparison doesn't check for between 2 dates, only that start date is less than or equal to EndDate

BetweenDates = (CDate(startDate) <= CDate(endDate))

The IIF is completely unnecessary. The above line returns a TRUE/FALSE value on its own.
Why are you passing the date as a string instead of an actual date value?

Please clarify what you are trying to do exactly.

 

[Pinaceous]

Your comparison doesn't check for between 2 dates, only that start date is less than or equal to EndDate

BetweenDates = (CDate(startDate) <= CDate(endDate))

The IIF is completely unnecessary. The above line returns a TRUE/FALSE value on its own.
Why are you passing the date as a string instead of an actual date value?

Please clarify what you are trying to do exactly.

Hi Scott,

Thank you for helping me with the code.

Could you please show me how I can pass the date as a value in the sub instead of as a string?

If I take out the string I'll just have:

startDate = "15/01/2015"
endDate = "25/03/2015"

Correct?

Thank you,
Paul

 

[Pinaceous]

What I'm trying to do is have the Sub operate in-between two dates. Where, the Sub will not operate outside of this condition.

For example,

In using:

startDate = "15/01/2015"
endDate = "25/03/2015"

If I go and type 3/30/18 the in Sheet1.Range("D3") the sub would produce Msgbox "Outside of date"

If I would type 1/31/15 the in Sheet1.Range("D3") the sub would produce Msgbox "Inside of date"

Hope this helps explain what I want to do.

Thank you!
Paul

 

[Scott Huish]

Your function needs a date to check, like this. Note that I'm using USA style dates in this example:

Function BetweenDates(checkdate As Date, startdate As Date, enddate As Date) As Boolean
BetweenDates = (checkdate >= startdate) And (checkdate <= enddate)
End Function

Sub Test()
Dim startdate As Date, enddate As Date
startdate = #1/15/2015#
enddate = #3/25/2015#

MsgBox BetweenDates(DateSerial(2015, 3, 13), startdate, enddate)
End Sub

 

[Pinaceous]

Hi Scott,

That will do it thank you!

Paul