vba sumifs 对日期感到困惑，四月结果错误
我正在关注这个其他堆栈溢出问题，并且非常开明，但是当我对结果进行健全性检查时，我在 4 月份遇到了这种特殊的奇怪行为，四月的任何 rev_date 都会产生 0 的结果。我尝试了不同的日期和 rev_date 和 grid_date 间隔，但发生同样的事情，我可以责怪系统日期配置吗？我缺少什么？
恕我直言，我宁愿使用 Excel 公式而不是 UDF，但这个奇怪的结果让我心痒痒。
使用此 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")
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´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")
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我按照你的方法做了，得到了正确的结果。您将日期设置硬编码为“dd mmm yyyy”，我在处理法语设置时发现它无法识别。通过控制面板检查您的设置，了解您拥有哪些区域设置。
另外，请检查您的日期 mm/dd 使用情况。更改单元格格式以指示月份名称而不是“05”。这样你就可以验证你没有颠倒日期和月份，并且每个日期确实是一个日期。
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.
不确定，但我认为在 VBA 中您应该始终使用美国日期格式。我通常将其定义为常量：
const kUsDate = "mm\/dd\/yyyy"反斜杠是转义字符。
作为证明，请尝试以下操作：在 B2 中输入类似 20/6/2011（法国风格）的日期，然后从 VBA 运行
Excel 将返回的代码：
m/d/yyyyCQFD;-)
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
Excel will return:
m/d/yyyyCQFD;-)