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.

Answer 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