Filter for all dates two months prior to current month

  • Hi, the below code filters last month and all months prior, but I would like to filter for two months prior ie in March I would like Jan and all months prior, cheers.

  • Hi,


    You can always use the worksheet function EDate (Arg1, Arg2)


    with Arg1 determined by your reference date

    and Arg2 equal to -2 to adjust date


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    To illustrate Edate with Evaluate ... you can test following macro

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Not sure to exactly understand what you need to filter out ...


    Isn't it ... Between Now and 2 Months prior to Now ... ?


    Do you mind attaching a small sample file ... to illustrate your expected result ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the sample file


    Code
    Sub Olderthan2Months()
    Dim startDate As Long
    Dim prevDate As Long
    startDate = DateSerial(Year(Now), Month(Now), Day(Now))
    prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    ' Adjust range to your specific situation '''''''''''''''''''''
    Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    End Sub
  • Would it possible to remove the 'Day' part........I only need it to be month specific ie we are in March so I only need all Jan and previous, not a specific day in Jan.


    Apart from that it work excellent....thanks heaps.

  • To replace the exact day .. you can keep always the 1 you had ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Do you mean like below......I tried and it only gets 1/1/21 instead of all Jan.


    Code
    Sub Olderthan2Months()
    Dim startDate As Long
    Dim prevDate As Long
    startDate = DateSerial(Year(Now), Month(Now),1) 'CHANGED THIS....ONLY GETS 1/1/21
    prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    ' Adjust range to your specific situation '''''''''''''''''''''
    Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    End Sub
  • Have a go with following


    Code
    Sub Olderthan2Months()
    Dim startDate As Long
    Dim prevDate As Long
    startDate = DateSerial(Year(Now), Month(Now) + 1, 1)
    prevDate = Evaluate("=Edate(" & startDate & ",-2)")
    ' Adjust range to your specific situation '''''''''''''''''''''
    Range("A1:A15").AutoFilter Field:=1, Criteria1:="<=" & prevDate
    End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi, the below code filters last month and all months prior, but I would like to filter for two months prior ie in March I would like Jan and all months prior, cheers.


    Hi, the below code filters last month and all months prior, but I would like to filter for two months prior ie in March I would like Jan and all months prior, cheers.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!