VBA Countifs not working with date

Hi,

I have a piece of code that will not work with date. I have other similar code that works with strings, numbers but this one is stumping me ...
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

It is supposed to give me a count of the number of rows with a date older than three days ago where the value in another column has "DM". r* variables refer to the ranges.

A formula in a cell in the spreadsheet works correctly ...

Any thoughts?

Are you defining your ranges properly? Your code, as such, works fine for me. In an blank workbook, run this, which should return a message "6"

Please Login or Register  to view this content.

Fàilte mcasserl,

It could be that your dates having time values attached to them. Since all dates are have two parts, the date portion and the time portion, this can effect the outcome of your results.

For example, today is 30 April 2014 is your "threedaysago" value. If a date you are comparing is 27 April 2014 should be true. Unless that date contains a time value that is not displayed. If the value was really 27 April 2014 10:30 AM, the compare will fail. Your "threedaysago" value starts at midnight because no time value is assigned. Does that make sense?

When you said "will not work" what did you mean - that it gave an incorrect value, gives a 0, or it just does not run?

VBA is US-centric, so it is possible that dates strings with ambiguous month/day combos (5/2/2014 is ambiguous, 5/21/2014 is not) may be interpreted M/D rather than D/M. Are the cell values true dates, or strings?

Can you keep a working formula on a sheet and just read the value from the cell?

There should be some dates that are not ambiguous, since we are at 4/30, and the dates should range from 4/15 to 4/30, then ambiguous dates that would not be counted anyway. I have no way to test solutions.... sorry.