Excel date autofilter problem with different date separators

[jfgreen123]

Hello all,

I have a worksheet with dates in the UK dd/mm/yy format. The macros I have written which include an autofilter on the dates works with no problems for my English colleagues.

However we have some German colleagues in the office who have their regional settings on German, so their default date format is dd.mm.yy. This causes a problem with the autofilter of the date.

I have tried two solutions:
1. Check the date separator format in the International property and code the date for the autofilter to use the correct separator.

If Application.International(xlDateSeparator) = "/" Then
ActiveSheet.Range("$B$3:$D$6").AutoFilter Field:=3, Criteria1:= _
">01/03/2014", Operator:=xlAnd, Criteria2:="<01/04/2014"
End If
If Application.International(xlDateSeparator) = "." Then
ActiveSheet.Range("$B$3:$D$6").AutoFilter Field:=3, Criteria1:= _
">01.03.2014", Operator:=xlAnd, Criteria2:="<01.04.2014"
End If

2. To use the DateSerial function in the autofilter.

ActiveSheet.Range("$B$3:$D$6").AutoFilter Field:=3, _
Criteria1:=">=" & DateSerial(2014, 3, 1), Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2014, 4, 1)

In both cases I get no data returned, but if I go manually into the autofilter drop down the values are there and a simple OK to the
dialogue box will return the correct results.

Any ideas what is going wrong, or how to correct it?

Thanks

John

 

[dmt32]

Dates and Autofilter is a bit of a problem if not using US date format. The trick that normally works is to pass your date to a long variable using dateserial.

See if approach below does what you want.

Sub FilterMonth()
    Dim FromMonth As Integer, FromYear As Long
    Dim ToMonth As Integer, ToYear As Long
    Dim MonthDays As Integer, FilterRange As Long
    Dim ldatefrom As Long, ldateto As Long

    FromMonth = 3
    FromYear = 2014

    ToMonth = 3
    ToYear = 2014

    MonthDays = Day(DateSerial(ToYear, ToMonth + 1, 1) - 1)

    ldatefrom = DateSerial(FromYear, FromMonth, 1)
    ldateto = DateSerial(ToYear, ToMonth, MonthDays)

    With ActiveSheet.Range("$B$3:$D$6")
            .AutoFilter
            .AutoFilter Field:=3, _
                        Criteria1:=">=" & ldatefrom, _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & ldateto
        FilterRange = .Columns(4).SpecialCells(xlCellTypeVisible).Count - 1
        
        If FilterRange = 0 Then
            MsgBox "No Dates Found In Range", 48, "No Dates Found"
            .Range("A1").AutoFilter
        End If
    End With
End Sub

Hope Helpful

Dave

 

[jfgreen123]

Hi Dave,

That works a treat in my test file, so now I'll add it into the actual programme.
Thanks very much for the help.

John

 

[dmt32]

Hi Dave,

That works a treat in my test file, so now I'll add it into the actual programme.
Thanks very much for the help.

John

Welcome - thanks for feedback

Dave