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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Hi Carim, thanks for your reply but it does exactly the same as my formula...Feb and previous months instead of Jan and previous months. More help would be much appreciated, cheers.

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Thanks for your Thanks ...AND for the Like :thumbup:

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

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    No problem ... you can adapt the operator ....

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

    Love 1

    Thanks a lot for your Thanks ...AND for the Like :thumbup:

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

    Love 1

Participate now!

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

Register Yourself Login