none
Change date format 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:50 PM

All replies

  • Hi GyTasS,

    Is it essential to use "AutoFileter"?
    If not, would you share sample text files, including date(s) in English, Danish, and Turkish format?
    I'd like to check if the date(s) can be specified and to be converted into English format. 

    Regards.
    Friday, February 5, 2016 4:00 AM
  • Hi Ashidacchi,

    Yes, it is essential to use "Autofilter"

    The date format in text file depends on the computer regional settings.

    If my settings are Danish, when I open the text file, I will see dates in Danish format and if your PC settings are Turkish, you will see dates in text file as Turkish format. As simple as that.

    Lets say column B contains data as follows:

    02.02.2016 14:50:00

    02.02.2016 14:50:15

    02.02.2016 15:50:30

    etc.

    Do you have an idea how I could use autofilter in column B between two dates?

    Kind regards,

    Gytis

    Sunday, February 7, 2016 7:17 PM
  • Please set your PC regional settings to Turkey and try to autofilter between dates.

    If you'll succeed, please inform me.

    Sunday, February 7, 2016 7:19 PM
  • Hi GyTasS,

    I imagine that "AutoFilter" is in your code.
    If so, how about this:
      1) Find which "Locale" is set on your computer.
           (refer to "Find the current user language")
           (refer to "Locale IDs Assigned by Microsoft")
      2) According to the "Locale", modify code of "AutoFilter".
    Would you provide your code via cloud storage such as OneDrive, Dropbox, etc?  
    Regards.
    • Edited by Ashidacchi Monday, February 8, 2016 1:16 AM
    Monday, February 8, 2016 1:05 AM
  • Hi Ashidacchi,

    I am using userform for a date input. And the code is attached below:

    Private Sub DataFilter()
    
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim StartPeriod As Date
        Dim starttimestr As String
        Dim EndPeriod As Date
        Dim endtimestr As String
        Dim StartDate As Date
        Dim StartTime As String
        Dim EndDate As Date
        Dim EndTime As String
    
        On Error GoTo ErrHandler
        
        With Sheets("Sheet1")
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If
            
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            StartDate = StartDateBox.Value
            StartTime = StartTimeBox.Value
            EndDate = EndDateBox.Value
            EndTime = EndTimeBox.Value
    
            
            StartPeriod = DateValue(StartDate) + TimeValue(StartTime)
            starttimestr = Format(StartPeriod, "m/d/yyyy hh:mm")
            EndPeriod = DateValue(EndDate) + TimeValue(EndTime)
            endtimestr = Format(EndPeriod, "m/d/yyyy hh:mm")
    
            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr
        End With
            
    Exit Sub
    ErrHandler:
        MsgBox ("Please enter the correct time!")
        ListBox1.Visible = False
        
    End Sub

    Monday, February 8, 2016 5:15 AM
  • This code works perfectly for Danish regional settings. However, when I have it to customer at Turkey,- Autofilter stopped working.
    Monday, February 8, 2016 5:19 AM