过滤器不适用于日期列超过3个月 [英] filter not working for date column more than 3 months

问题描述

我试图在3列中放置3个过滤器，其中一个是资源名称，第二个是状态，第三个是日期.对于每个资源名称，我需要找到在当月前三个月创建的票证，并且状态"列应具有2个值:新建"和打开".我写了下面的代码，但返回的结果是0.如果我将第3个过滤条件更改为>"& premier_jour_3，它向我显示了3个月前的结果以及Premier_jour_3之后的最新结果.如果premier_jour_3 = 01/11/2017，则显示日期为"07/09/2017 17:59:00"和"31/07/2017 10:35:00"的记录

I am trying to put 3 filters in 3 columns, in which one is resource name, the second is status, the third is date. For each resource name, I need to find a ticket which was created 3 months before the current month, and status column should have 2 values, "New" and "Open". I wrote the below code, but it is giving me 0 result in return. If I am changing the 3rd filter criteria to ">" & premier_jour_3, it is showing me results before than 3 months and the latest ones after premier_jour_3. If premier_jour_3 = 01/11/2017, it is showing the records with date "07/09/2017 17:59:00" and "31/07/2017 10:35:00"

   premier_jour_3 = DateSerial(Year(Date), Month(Date) - 3, 1)
With Worksheets("general_report")
        .AutoFilterMode = False
        .Range("A1:R100000").Select
        Selection.AutoFilter

    Worksheets("general_report").Range("C1").AutoFilter Field:=3, Criteria1:="ResourceName", Operator:=xlAnd
    Worksheets("general_report").Range("F1").AutoFilter Field:=6, Criteria1:=Array("Opened", "New"), Operator:=xlFilterValues
    Worksheets("general_report").Range("H1").AutoFilter Field:=8, Criteria1:="<" & premier_jour_3, Operator:=xlAnd
    row_cnt_nt_upd = Evaluate("=subtotal(3,A:A)") - 1
End With

推荐答案

.AutoFilter喜欢其字符串形式的条件.对于日期，无论区域设置如何，该字符串都必须为mm/dd/yyyy或MDY的EN-US格式.

The .AutoFilter likes its criteria in strings. In the case of dates, this string must be in the EN-US format of mm/dd/yyyy or MDY regardless of regional settings.

premier_jour_3 = DateSerial(Year(Date), Month(Date) - 3, 1)

With Worksheets("general_report")
    if .AutoFilterMode  then .AutoFilterMode = False
    with .Range("A1:R100000")
        .AutoFilter Field:=3, Criteria1:="ResourceName"
        .AutoFilter Field:=6, Criteria1:=Array("Opened", "New"), Operator:=xlFilterValues
        .AutoFilter Field:=8, Criteria1:=format(premier_jour_3, "\<mm/dd/yyyy") 

        row_cnt_nt_upd = Evaluate("=subtotal(3,A:A)") - 1
    end with
End With

这篇关于过滤器不适用于日期列超过3个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！