VBA SUMIFs对日期感到困惑 - 产生不正确的结果 [英] VBA SUMIFs getting confused about dates - producing incorrect result

问题描述

以下代码产生错误的结果。在工作表中，我们使用两个相同的日期格式（dd / mm / yyyy），但是当运行以下操作时，它似乎试图将 rev_date 解释为美国日期格式，同时解释 grid_date 作为正确的英国格式。

The below code is producing the wrong result. On the work sheet we are using two identical date formats (dd/mm/yyyy) however when the following is run it appears to be trying to interpret the rev_date as American date format, whilst interpreting grid_date as the correct UK format.

我们通过将工作表上的rev_date更改为美国格式进行了测试，在这种情况下，它将产生正确的结果。

We tested this by changing the rev_date on the worksheet to the American format, in which case it produces the correct result.

为什么我们需要将rev_date更改为美国格式的任何想法，我们宁愿将其保留为英国？

Any ideas why we would need to change rev_date to an American format, we would prefer to keep it as UK?

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


  Dim Order_Type As Range
  Dim Final_Price As Range
  Dim PaidAlt As Range
  Dim Excl_Rev As Range
  Dim PAmount1 As Range
  Dim PMethod1 As Range
  Dim PAmount2 As Range
  Dim PayDate2 As Range
  Dim PMethod2 As Range
  Dim Vstatus As Range
  Dim Team As Range

  Application.Volatile (True)

  Set Order_Type = Sheets("KRONOS").Range("$D:$D")
  Set Final_Price = Sheets("KRONOS").Range("$H:$H")
  Set PaidAlt = Sheets("KRONOS").Range("$I:$I")
  Set Excl_Rev = Sheets("KRONOS").Range("$K:$K")
  Set PAmount1 = Sheets("KRONOS").Range("$O:$O")
  Set First_PD = Sheets("KRONOS").Range("$Q:$Q")
  Set PMethod1 = Sheets("KRONOS").Range("$R:$R")
  Set PAmount2 = Sheets("KRONOS").Range("$T:$T")
  Set PayDate2 = Sheets("KRONOS").Range("$V:$V")
  Set PMethod2 = Sheets("KRONOS").Range("$W:$W")
  Set Vstatus = Sheets("KRONOS").Range("$DL:$DL")
  Set Team = Sheets("KRONOS").Range("$DO:$DO")

            GRIDSALES1 = Application.WorksheetFunction.SumIfs( _
            PAmount1 _
            , Team, "<>9" _
            , Vstatus, "<>rejected", Vstatus, "<>unverified" _
            , Excl_Rev, "<>1" _
            , PMethod1, "<>Credit" _
            , PMethod1, "<>Amendment" _
            , PMethod1, "<>Pre-paid" _
            , First_PD, ">=" & rev_date _
            , First_PD, "<=" & Application.WorksheetFunction.EoMonth(grid_date, 0))

            GRIDSALES = GRIDSALES1

结束功能

推荐答案

当您这样做

First_PD, ">=" & rev_date

这个

First_PD, "<=" & Application.WorksheetFunction.EoMonth(grid_date, 0))

您隐含强制两个类型为$的变量c $ c>日期（ rev_date 和 grid_date ）转换为 String 。隐性胁迫是不好的做法，这是一个非常典型的例子，为什么它是坏的做法。您的日期应使用明确的格式显式转换为 String 。

you are implicitly coercing two variables of type Date (rev_date and grid_date) into type String. Implicit coercion is bad practice, and this is a pretty typical example why it's bad practice. Your dates should be explicitly converted to String using an unambiguous format, e.g.

First_PD, ">=" & Format$(rev_date,"dd mmm yyyy") 

在这种格式下，2011年1月12日与2011年12月1日混淆。

In this format, 12 Jan 2011 can't be confused with 1 Dec 2011.

这篇关于VBA SUMIFs对日期感到困惑 - 产生不正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！