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
– Harley B, Commented Aug 17, 2014 at 14:08
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
– ivan_pozdeev, Commented Sep 1, 2017 at 13:19

Gary's Student · Accepted Answer · 2014-08-17 14:20:02Z

-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, 2014 at 14:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harley B Over a year ago

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 Over a year ago

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.

Gary's Student Over a year ago

@HarleyB Perhaps the # forces a conversion to Date

Collectives™ on Stack Overflow

Worksheetfunction.countifs - doesn't recognise dates?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related