VBA for Date

[M_S]

Hello,

I have a spreadsheet wherein dates are stored as Text, I want to convert them to date format mm/dd/yyyy and then filter dates to NOT include current month. So, for example I don't want anything from Sept to show up, I only want prior months. Please note, I have other data on the left & right of date column. I want to do this using VBA.

Any help would be much appreciated. Thanks!

Date
2021-08-25
2021-08-25
2021-08-25
2021-01-14
2021-05-31
2021-05-31
2021-09-03
2021-09-03
2021-09-03

 

[JGordon11]

Sub ConvertToDateAndFilter()
    Dim A, i As Long
    With Range("A2:A10")
        .NumberFormat = "mm/dd/yyyy"
        A = .Value
        For i = 1 To UBound(A)
            A(i, 1) = DateValue(A(i, 1))
        Next
        .Value = A
        .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day(DateValue(Date))
    End With
End Sub

201914.xlsx
	A
1	Date
2	2021-08-25
3	2021-08-25
4	2021-08-25
5	2021-01-14
6	2021-05-31
7	2021-05-31
8	2021-09-03
9	2021-09-03
10	2021-09-03
Sheet3

201914.xlsx
	A
1	Date
2	08/25/2021
3	08/25/2021
4	08/25/2021
5	01/14/2021
6	05/31/2021
7	05/31/2021
Sheet3

 

[Micron]

Lots of things I should be doing besides trying to learn Excel vba (much better at Access vba). I was so close yet so far on the filtering part! I didn't know about the "operator" part and the filtering wasn't right even after more than an hour (I had """") at the very end. M$ documentation is such that if you don't really know the model, it's hard to find anything. Thanks for the lesson. My code finally works, but I suppose OP will need a dynamic range?

Dim theDate As Date
Dim mth As Integer, yr As Integer

mth = Month(Date)
yr = Year(Date)

theDate = DateSerial(yr, mth, 1)

    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$9").AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & theDate

 

[JGordon11]

Modified to have range specified from inputbox:

Sub ConvertToDateAndFilter()
    Dim A, i As Long, Rng As Range
    On Error GoTo ExitSub 'if user cancels out of input box
        Set Rng = Application.InputBox("Select Range", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    With Rng
        .NumberFormat = "mm/dd/yyyy"
        A = .Value
        On Error Resume Next 'if selection includes data that can't convert to date
            For i = 1 To UBound(A)
                A(i, 1) = DateValue(A(i, 1))
            Next
        On Error GoTo 0
        .Value = A
        .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day((Date))
    End With
ExitSub:
End Sub

 

[Rick Rothstein]

Sub ConvertToDateAndFilter()
    Dim A, i As Long
    With Range("A2:A10")
        .NumberFormat = "mm/dd/yyyy"
        A = .Value
        For i = 1 To UBound(A)
            A(i, 1) = DateValue(A(i, 1))
        Next
        .Value = A
        .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day(DateValue(Date))
    End With
End Sub

Because of the way the text dates are written, you can write your code more compactly this way...

Sub ConvertToDateAndFilter()
  With Range("A2:A10")
    .TextToColumns
    .NumberFormat = "mm/dd/yyyy"
    .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day(DateValue(Date))
  End With
End Sub

 

[M_S]

Because of the way the text dates are written, you can write your code more compactly this way...

Sub ConvertToDateAndFilter()
  With Range("A2:A10")
    .TextToColumns
    .NumberFormat = "mm/dd/yyyy"
    .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day(DateValue(Date))
  End With
End Sub

Thank you for this, it is converting the date from text to correct format. However, it's not filtering the previous months. 
[/QUOTE]

 

[M_S]

Sub ConvertToDateAndFilter()
    Dim A, i As Long
    With Range("A2:A10")
        .NumberFormat = "mm/dd/yyyy"
        A = .Value
        For i = 1 To UBound(A)
            A(i, 1) = DateValue(A(i, 1))
        Next
        .Value = A
        .AutoFilter Field:=1, Operator:=xlAnd, Criteria1:="<=" & Date - Day(DateValue(Date))
    End With
End Sub

201914.xlsx
	A
1	Date
2	2021-08-25
3	2021-08-25
4	2021-08-25
5	2021-01-14
6	2021-05-31
7	2021-05-31
8	2021-09-03
9	2021-09-03
10	2021-09-03
Sheet3

201914.xlsx
	A
1	Date
2	08/25/2021
3	08/25/2021
4	08/25/2021
5	01/14/2021
6	05/31/2021
7	05/31/2021
Sheet3

Thank you!