CODEWITHSUNDEEP
vbaexcelcriteriacountif
eeeccc17
eeeccc17

Reputation: 55

using string values for DATE criterias for COUNTIF in vba excel

I have 2 cells in Range B:B, which I wish to apply countif formula to via VBA in Excel.

27/09/2017
13/06/2018

I have tried 

MsgBox (WorksheetFunction.CountIf(Range("B:B"), "27/09/2017"))

This returns 0

MsgBox (WorksheetFunction.CountIf(Range("B:B"), CDate("27/09/2017")))

This returns 1

I am trying to figure out how I can get the macro to return both date values, via

MsgBox (WorksheetFunction.CountIf(Range("B:B"), "<=" & CDate("13/06/2018")))

However this returns 0

This seems to indicate that I do not quite understand how to filter dates in countif via strings. Would it be possible to share a alternative?

I hope to implement a text input box where the user can input a date string which then applies the countif on

Upvotes: 2

Views: 1407

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need to convert the date into a double first to compare the actual value of the date independent from the format.

 "<=" & CDbl(CDate("13/06/2018"))

Why is this?
If you concatenate a string with a date like in "<=" & CDate("13/06/2018") the date is converted into a string and the result is "<=13/06/2018" (here the date is a string not a value).

If you convert the date into a double CDbl(CDate("13/06/2018") the result is 43264 which is the serial number that represents the date. Dates in Excel are stored as serial numbers counting from 1900-01-01 so 2018-06-13 is day 43264 since 1900-01-01.

So with "<=" & CDbl(CDate("13/06/2018")) you actually compare the cell value to "<=43264". So you compare to a value and not a text. This works because if a cell is formatted as date Excel actually stores the serial value but shows the formatted string for user compatibility.

Conclusion
If you want to compare dates always compare their values not their strings.

Also see: How to use dates and times in Excel.

Upvotes: 1

Related Questions