Countif in VBA returns wrong result.

[bebo021999]

I have a list of date in A3:A13 and 1 date in C1
All dates are formatted as dd/mm/yyy
I try to countif as follow:
=COUNTIF(A3:A13,"<="&C1)
it is OK
but try with VBA:

Please Login or Register  to view this content.

it returns wrong result
See attachment.
Anybody can help?

[StuCram]

it appears that the wrong variable is being used (ngay) as your function's first parameter. Possibly a typo.

This routine worked for me.

Please Login or Register  to view this content.

[bebo021999]

Yes, it is typo. At the first time I used "ngay"(date in my language) as variable.
Anyway, it still does not work with your code.

[DBY]

Hello
The wrong variable wasn't the issue for me. When I corrected it I still got 11 with the macro. With me in the UK it's always that VBA sees the dates as US and miscalculates. I convert the date to Long to avoid this. This works for me:

Please Login or Register  to view this content.

DBY

[bebo021999]

Thanks DBY, it works now.
it is first time I hear about this and it seem strange to me.
Anyway, it must be OK if dates are formatted as yyyy/mm/dd.
I tried but it is still wrong!!

[DBY]

Yes it frustrates me too. Formatting never seems to help. I tried a number of solutions but converting to long seems to work in cases like this. I'm not clear as to the logic that's at work here!

[karedog]

That's because you use regional setting that use dd/mm/yyyy syntax (for Vietnam, I guess ?), but mostly VBA commands use English (United States) regional setting (mm/dd/yyyy).

n = WorksheetFunction.CountIf(adate, ">=" & Range("C1"))
With C1 = 03/01/2016, this line is treated as :
n = WorksheetFunction.CountIf(adate, ">=" & 1 March 2016)
rather than :
n = WorksheetFunction.CountIf(adate, ">=" & 3 Jan 2016)

For experiment, (close Excel first) try changing your windows regional setting (through control panel) to "English (United States)" and then run the sub, you will get correct answer.

The best way to deal with date is like DBY suggestion, using clng(range()) or cdbl(range()) or range().value2