none
Formating datavalue as independet of the Windows regional setting for date-format RRS feed

  • Question

  • How to format my datavalue to be independet of the Windows regional setting for date-format?

    I have a table in Excel worksheet with one column (Column B) containing date range in each cell as follows (dd-mm-yyyy hh:mm:ss). I want to use AutoFilter to filter data between two dates from column B. Autofilter only works with US regional settings at the control panel.... How should I format my code to run on for instance Danish regional settings?

    This is the peace of my code which does not work as intended (it works only with US regional settings):

    Sub selectrange()
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim starttime As Date
        Dim starttimestr As String
        Dim endtime As Date
        Dim endtimestr As String
        Dim selectedCells As Range
        Dim stime As String
        Dim etime As String
        
        stime = InputBox ("start time")
        etime = InputBox ("end time")
        
        With Sheets("Sheet1")
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If

            .Columns("B:B").NumberFormat = "dd-mm-yyyy hh:mm"
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            starttime = DateValue(stime) + TimeValue(stime)
            starttimestr = Format(starttime, "dd-mm-yyyy hh:mm")
            endtime = DateValue(etime) + TimeValue(etime)
            endtimestr = Format(endtime, "dd-mm-yyyy hh:mm")
                
            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr
                
             Set selectedCells = .Range(.Range("A1"), .Cells(LastRow, LastCol)).SpecialCells(xlCellTypeVisible)
        End With
        With Sheets("sheet2")
           selectedCells.Copy _
              Destination:=.Range("A1")
        End With
    End Sub

    Monday, December 15, 2014 9:56 PM

Answers

  • Try this, adapting your code

            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & Format(starttimestr, "m/d/yyyy hh:mm"), _
               Operator:=xlAnd, _
               Criteria2:="<=" & Format(endtimestr, "m/d/yyyy hh:mm")


    • Marked as answer by GyTasS Thursday, December 18, 2014 8:33 AM
    Monday, December 15, 2014 10:38 PM
    Moderator

All replies

  • Try this, adapting your code

            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & Format(starttimestr, "m/d/yyyy hh:mm"), _
               Operator:=xlAnd, _
               Criteria2:="<=" & Format(endtimestr, "m/d/yyyy hh:mm")


    • Marked as answer by GyTasS Thursday, December 18, 2014 8:33 AM
    Monday, December 15, 2014 10:38 PM
    Moderator
  • Thank you! Works perfect!
    Thursday, December 18, 2014 8:34 AM