Worksheetfunction.countifs - doesn't recognise dates?

Question

As part of a larger project I'm trying to get a count of the instances of dates between a search range.

So for example my date range is 02/01/2014 to 04/01/2014 and in my search range the date 02/01/2014 appears 4 times, 03/01/2014 appears 3 times and 04/01/2014 appears 3 times then the count would return a value of 10.

I can get this to work with numbers - so similarly assuming my search range is 2 to 4 and in my search range 2 appears 4 times, 3 appears 3 times and 4 appears 3 times then the count does return 10. But when I do exactly the same with dates then the count returns 0.

So here's the 2 test codes - I have numbers in column A and dates in column B

Sub testnumbers()
Dim Val As Double
    Val = Application.WorksheetFunction.CountIfs(Range("A:A"), ">=2", Range("A:A"), "<=4")
MsgBox Val

End Sub

Returns the correct count in Val. While

Sub testdates()
Dim Val As Double
    Val = Application.WorksheetFunction.CountIfs(Range("B:B"), ">=02/01/2014", Range("B:B"), "<=04/01/2014")
MsgBox Val

End Sub

returns a value of 0 to Val as if it hasn't found any instances of any dates in the range.

Can anyone see where I'm going wrong? It just doesn't seem to see the dates properly...

Any help much appreciated.

Also, these work in Excel outside VBA without any issue. Only in VBA does the date one return a 0... — Harley B, Aug 17 '14 at 14:08
Possible duplicate of [Excel VBA Countif between dates](https://stackoverflow.com/questions/24644714/excel-vba-countif-between-dates) — ivan_pozdeev, Sep 01 '17 at 13:17
The suggested duplicate has the real solution. You were probably just lucky in that the internal representation of something happened to be in range. — ivan_pozdeev, Sep 01 '17 at 13:19

score -1 · Accepted Answer · answered Aug 17 '14 at 14:20

-1

Try this:

Sub testdates()
    Dim Val As Double, wf As WorksheetFunction, r As Range, _
        d1 As Date, d2 As Date

    Set wf = Application.WorksheetFunction
    Set r = Range("B:B")
    d1 = DateValue("2/1/2014")
    d2 = DateValue("4/1/2014")

    Val = wf.CountIfs(r, ">=" & d1, r, "<=" & d2)
    MsgBox Val
End Sub

answered Aug 17 '14 at 14:20

Gary's Student

95,722
10
59
99

Thanks for the quick reply! Still no joy on that one I'm afraid... I still get a 0 returned to the Val variable :( – Harley B Aug 17 '14 at 14:26
Cracked it (through trying random stuff more than anything).. the date variables (d1 and d2 in your example) have to be set between #. So it would be 'd1 = #2/1/2014#' and that for some reason allows VBA to compare the date variables to the dates in the list. – Harley B Aug 17 '14 at 14:28
@HarleyB Perhaps the **#** forces a conversion to *Date* – Gary's Student Aug 17 '14 at 14:47

Worksheetfunction.countifs - doesn't recognise dates?

1 Answers1