VBA: check date between 2 dates

[LolaM]

Hi folks, looking for a bit of help here with the following:

I have a range of cells, holding date values. These cells could also be empty.

I have a 'start' and an 'end' date, and I want to check each date in the range mentioned above falls between the start and end dates.

If date falls between start and end dates, no action. If the date cell is empty, no action.

If date falls outside either the start or end dates, increment a counter.

Repeat for all cells in the range.

I've tried with a For Each and it's successfully moving thru the range, but I'm having issues getting the logic and sequence of the If...Then...Else correct.

I'm back in the world of VBA after a few years of not having to use it, but due to a role change I'm back now and my skills are rusty!

Thanks in advance

 

[Joe4]

Why do you need VBA for this?
You should be able to do that with an Excel formula, like this.

Here is an example, that counts the number of records between cells A2 and A10 that fall between the dates in cells D1 and D2:

formula in cell A12:

=COUNT(A2:A10)-COUNTIFS(A2:A10,">="&D1,A2:A10,"<="&D2)

 

[LolaM]

Why do you need VBA for this?
You should be able to do that with an Excel formula, like this.

Here is an example, that counts the number of records between cells A2 and A10 that fall between the dates in cells D1 and D2:

View attachment 120278

formula in cell A12:

=COUNT(A2:A10)-COUNTIFS(A2:A10,">="&D1,A2:A10,"<="&D2)

Thanks, but it needs to be done in VBA because it's part of an automated checklist of ~40 other questions. The range of dates to be checked will vary in size, the start and end dates are variables in named ranges.

 

[Joe4]

You really haven't given us enough details to create code to do this for you (without us making up the layout - unless you are looking for some sort of generic User Defined Function you can call in VBA).

Where exactly do the Start Date and End Date come from?

What are the rules around the range to check?
Does it always start in a certain cell, and go down to the bottom of that list (which can change)?
Or is it in some range selection?

It might be helpful to show us some sample data and any pertinent VBA code you already have.

 

[Joe4]

OK, I went with creating a generic User Defined Function that you can easily call and re-use, and enter in your variables.

First, here is the code:

Function CountOutliers(rngData As Range, stDate As Date, endDate As Date) As Long

    Dim cell As Range
    Dim ct As Long
    
'   Loop through all the cells in the data range
    For Each cell In rngData
'       Check to see if non-blank entry in cell and it is a date
        If cell <> "" And IsDate(cell) Then
'           Check to see if the date is before the start date or after the end date
            If cell < stDate Or cell > endDate Then
'               Add one to counter
                ct = ct + 1
            End If
        End If
    Next cell
    
'   Return count of outliers
    CountOutliers = ct

End Function

And here is a test on the example I provided in my earlier reply:

Sub Test()
    Dim myCount As Long
    myCount = CountOutliers(Range("A2:A10"), Range("D1"), Range("D2"))
    MsgBox myCount
End Sub

When I run it, it returns the number "4", as I would expect from that example.

Note that while I used hard-coded ranges in that Test example above, you can use Range variables instead.

This should do what you want.

 

[LolaM]

OK, I went with creating a generic User Defined Function that you can easily call and re-use, and enter in your variables.

First, here is the code:

Function CountOutliers(rngData As Range, stDate As Date, endDate As Date) As Long

    Dim cell As Range
    Dim ct As Long
   
'   Loop through all the cells in the data range
    For Each cell In rngData
'       Check to see if non-blank entry in cell and it is a date
        If cell <> "" And IsDate(cell) Then
'           Check to see if the date is before the start date or after the end date
            If cell < stDate Or cell > endDate Then
'               Add one to counter
                ct = ct + 1
            End If
        End If
    Next cell
   
'   Return count of outliers
    CountOutliers = ct

End Function

And here is a test on the example I provided in my earlier reply:

Sub Test()
    Dim myCount As Long
    myCount = CountOutliers(Range("A2:A10"), Range("D1"), Range("D2"))
    MsgBox myCount
End Sub

When I run it, it returns the number "4", as I would expect from that example.

Note that while I used hard-coded ranges in that Test example above, you can use Range variables instead.

This should do what you want.

Worked perfectly, thanks so much!

 

[Joe4]

You are welcome.
Glad I was able to help!