Changing the date format in InputBox to AutoFilter by

[AceSpace]

Hello! I would like to know if there's a way to accept a date in the dd.mm.yyyy format in an InputBox and then use it for the AutoFilter by date function (which to my knowledge uses the mm/dd/yyyy format). What should I do?

 

[6StringJazzer]

If you are talking about InputBox you must be developing a VBA solution but you haven't shown any of your other code, so I'm not sure where you're going with this.

Here is how you get a proper date string based on the user input you described. This is just a framework; you have to incorporate this into your code.

Public Sub convertit()

   Dim DateString As String
   Dim DateD As Date
   Dim FilterDate As String
  
   DateString = InputBox(prompt:="Enter date dd.mm.yyyy", Title:="Enter Date")

   If DateString = "" Then
      MsgBox "User cancelled."
   ElseIf Not DateString Like "[0-9][0-9].[0-1][0-9].20[0-3][0-9]" And _
          Not DateString Like "[0-9][0-9].[0-1][0-9].19[0-9][0-9]" _
      Then
      MsgBox DateString & " is not a valid date in the form dd.mm.yyyy"
   Else
      On Error GoTo BadDate
      DateD = DateValue(Left(DateString, 2) & "/" & Mid(DateString, 4, 2) & "/" & Right(DateString, 4))
      On Error GoTo 0
     
      FilterDate = Format(DateD, "m/d/yyyy")
      MsgBox "Use " & FilterDate & " to build AutoFilter"
     
   End If
  
   Exit Sub
  
BadDate:
   MsgBox DateString & " is in the correct format but is not a valid date"
  
End Sub

Why don't just require the user to enter the date in mm/dd/yyyy format to begin with?