vba sumifs getting confused about dates, wrong result in april

I´m following this other Stack Overflow question, and was very enlightened, but when I made a sanity check of the results I get this particular odd behavior in April, any rev_date of april yields a result of 0. I have tried different dates and rev_date and grid_date intervals, but the same thing occurs, Can I blame the system date configuration? what am I missing?

IMHO I would rather use the in-excel formulation instead the UDF, but this odd result is itching in my mind.

using this UDF:

Public Function GRIDSALES(rev_date As Date, grid_date As Date) As Double

  Dim Team As Range
  Dim First_PD As Range
  Dim PAmount1 As Range

  Application.Volatile (True)

  Set PAmount1 = Sheets("Sheet1").Range("$F6:$F12")
  Set First_PD = Sheets("Sheet1").Range("$E6:$E12")
  Set Team = Sheets("Sheet1").Range("$D6:$D12")

     开发者_Go百科       GRIDSALES = Application.WorksheetFunction.SumIfs( _
            PAmount1 _
            , Team, "<>9" _
            , First_PD, ">=" & Format$(rev_date, "dd mmm yyyy") _
            , First_PD, "<=" & Format$(Application.WorksheetFunction.EoMonth(grid_date, 0), "dd mmm yyyy"))

End Function

I followed what you had and I get the correct result. You are hard coding the date settings to "dd mmm yyyy", which I have found when dealing with french settings, isn't recognizable. Check your settings through the control panel for what regional settings you've got. Also, check your date mm/dd usage. Change the cell formatting to indicate the month name instead of '05'. That way you can verify you aren't reversing the days and months And every date really is a date.

Not sure, but I think in VBA you should always use the US date format. I generally define it in a constant:
const kUsDate = "mm\/dd\/yyyy"
Backslash is the escape character.
As a proof, try this: enter a date like 20/6/2011 (french style) in B2, then from VBA, run the code

Debug.Print ActiveCell.NumberFormat

Excel will return: m/d/yyyy CQFD
;-)