none
Changing of date format for AutoFilter RRS feed

  • Question

  • I have made a routine to read a text file which contains different type of data.

    One of the columns when text file is imported in the spread sheet contains date values. The format of the date values in that column (lets say column "B") depends on the regional settings. So in case PC is set to English (United States), date  format in a cell appeards as follows: "m/d/yyyy hh:mm:ss" while with Danish regional setting "dd-mm-yyyy hh:mm:ss" another story is  in Turkish "dd.mm.yyyy hh:mm:ss".

    The problem here is that I am using an AutoFilter to filter between dates and AutoFilter only recognizes English (United States) date format. That is how AutoFilter object is described in forum:

    "When using AutoFilter with dates, the format should be consistent with English date separators ("/") instead of local settings ("."). A valid date would be "2/2/2007", whereas "2.2.2007" is invalid."

    Can somebody help to find a way to use AutoFilter independently of regional settings? I want Autofilter work for different date formats... Or the solution would be to change date format in column that would fit the Autofilter requirements?

    Thursday, February 4, 2016 9:51 PM

Answers

  • >>>Can somebody help to find a way to use AutoFilter independently of regional settings? I want Autofilter work for different date formats... Or the solution would be to change date format in

    column that would fit the Autofilter requirements?

    According to your description, since Excel sees the dates as a US date, not the format you use in Windows Regional Settings, one way around this problem is to pass the date to a Long Variable using the DateSerial function. All dates in Excel are stored as Long integers, refer to below code:

    Sub FilterByDate()
    
        Dim dDate As Date
        Dim strDate As String
        Dim lDate As Long
    
        dDate = DateSerial(2006, 8, 12)
        lDate = dDate
    
        Range("A1").AutoFilter
        Range("A1").AutoFilter Field:=1, Criteria1:=">" & lDate
    
    End Sub
    
    For more information, click here to refer about Range.AutoFilter Method (Excel)

    • Proposed as answer by André Santo Friday, February 5, 2016 12:50 PM
    • Marked as answer by David_JunFeng Tuesday, February 16, 2016 9:50 AM
    Friday, February 5, 2016 1:52 AM