Filter between 2 date ranges

Multi tool use
Multi tool use












0















I would like to filter 1 lot of data between 2 date ranges.



Date range 1: Going back from yesterday's date to 28 days previous.

Date range 2: Going back one year from yesterday's date to 28 days previous.



To filter to one date range I have this code working:



Sub DateFilter()

Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1

Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(EndDateTY)

End Sub


I was thinking I could use arrays to use 2 different date ranges but can't get it working.



Sub DateFilter2Ranges()

Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1

Dim StartDateLY As Date
Dim EndDateLY As Date
StartDateLY = Date - 29 - 365
EndDateLY = Date - 1 - 365

Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))

End Sub


I also thought I could mix 'And' & 'Or' operators if this is possible.










share|improve this question





























    0















    I would like to filter 1 lot of data between 2 date ranges.



    Date range 1: Going back from yesterday's date to 28 days previous.

    Date range 2: Going back one year from yesterday's date to 28 days previous.



    To filter to one date range I have this code working:



    Sub DateFilter()

    Dim StartDateTY As Date
    Dim EndDateTY As Date
    StartDateTY = Date - 29
    EndDateTY = Date - 1

    Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
    Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
    Criteria2:="<=" & CDbl(EndDateTY)

    End Sub


    I was thinking I could use arrays to use 2 different date ranges but can't get it working.



    Sub DateFilter2Ranges()

    Dim StartDateTY As Date
    Dim EndDateTY As Date
    StartDateTY = Date - 29
    EndDateTY = Date - 1

    Dim StartDateLY As Date
    Dim EndDateLY As Date
    StartDateLY = Date - 29 - 365
    EndDateLY = Date - 1 - 365

    Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
    Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
    Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))

    End Sub


    I also thought I could mix 'And' & 'Or' operators if this is possible.










    share|improve this question



























      0












      0








      0








      I would like to filter 1 lot of data between 2 date ranges.



      Date range 1: Going back from yesterday's date to 28 days previous.

      Date range 2: Going back one year from yesterday's date to 28 days previous.



      To filter to one date range I have this code working:



      Sub DateFilter()

      Dim StartDateTY As Date
      Dim EndDateTY As Date
      StartDateTY = Date - 29
      EndDateTY = Date - 1

      Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
      Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
      Criteria2:="<=" & CDbl(EndDateTY)

      End Sub


      I was thinking I could use arrays to use 2 different date ranges but can't get it working.



      Sub DateFilter2Ranges()

      Dim StartDateTY As Date
      Dim EndDateTY As Date
      StartDateTY = Date - 29
      EndDateTY = Date - 1

      Dim StartDateLY As Date
      Dim EndDateLY As Date
      StartDateLY = Date - 29 - 365
      EndDateLY = Date - 1 - 365

      Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
      Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
      Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))

      End Sub


      I also thought I could mix 'And' & 'Or' operators if this is possible.










      share|improve this question
















      I would like to filter 1 lot of data between 2 date ranges.



      Date range 1: Going back from yesterday's date to 28 days previous.

      Date range 2: Going back one year from yesterday's date to 28 days previous.



      To filter to one date range I have this code working:



      Sub DateFilter()

      Dim StartDateTY As Date
      Dim EndDateTY As Date
      StartDateTY = Date - 29
      EndDateTY = Date - 1

      Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
      Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
      Criteria2:="<=" & CDbl(EndDateTY)

      End Sub


      I was thinking I could use arrays to use 2 different date ranges but can't get it working.



      Sub DateFilter2Ranges()

      Dim StartDateTY As Date
      Dim EndDateTY As Date
      StartDateTY = Date - 29
      EndDateTY = Date - 1

      Dim StartDateLY As Date
      Dim EndDateLY As Date
      StartDateLY = Date - 29 - 365
      EndDateLY = Date - 1 - 365

      Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
      Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
      Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))

      End Sub


      I also thought I could mix 'And' & 'Or' operators if this is possible.







      excel vba filtering






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 5 at 18:43









      Community

      11




      11










      asked Jan 2 at 11:32









      Andrew WhittyAndrew Whitty

      12




      12
























          2 Answers
          2






          active

          oldest

          votes


















          1














          In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.



          Sub FFF()

          Dim dt1 As Date, dt2 As Date
          Dim sDate1$, sDate2

          '// Get dates
          dt1 = Now - 29: dt2 = Now - 1

          '// Format dates
          sDate1 = Format(dt1, "MM/yy/yyyy")
          sDate2 = Format(dt2, "MM/yy/yyyy")

          With Sheets("Main").Range("A1").CurrentRegion
          .AutoFilter
          .AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2

          End With

          End Sub





          share|improve this answer































            -1














            Simple code to filter the dates for your ranges, modify to meet your needs...



            With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
            'Do something

            .AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
            'Do something

            End With





            share|improve this answer





















            • 1





              1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

              – JohnyL
              Jan 5 at 19:33











            • @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

              – GMalc
              Jan 6 at 19:20











            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005579%2ffilter-between-2-date-ranges%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1














            In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.



            Sub FFF()

            Dim dt1 As Date, dt2 As Date
            Dim sDate1$, sDate2

            '// Get dates
            dt1 = Now - 29: dt2 = Now - 1

            '// Format dates
            sDate1 = Format(dt1, "MM/yy/yyyy")
            sDate2 = Format(dt2, "MM/yy/yyyy")

            With Sheets("Main").Range("A1").CurrentRegion
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2

            End With

            End Sub





            share|improve this answer




























              1














              In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.



              Sub FFF()

              Dim dt1 As Date, dt2 As Date
              Dim sDate1$, sDate2

              '// Get dates
              dt1 = Now - 29: dt2 = Now - 1

              '// Format dates
              sDate1 = Format(dt1, "MM/yy/yyyy")
              sDate2 = Format(dt2, "MM/yy/yyyy")

              With Sheets("Main").Range("A1").CurrentRegion
              .AutoFilter
              .AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2

              End With

              End Sub





              share|improve this answer


























                1












                1








                1







                In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.



                Sub FFF()

                Dim dt1 As Date, dt2 As Date
                Dim sDate1$, sDate2

                '// Get dates
                dt1 = Now - 29: dt2 = Now - 1

                '// Format dates
                sDate1 = Format(dt1, "MM/yy/yyyy")
                sDate2 = Format(dt2, "MM/yy/yyyy")

                With Sheets("Main").Range("A1").CurrentRegion
                .AutoFilter
                .AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2

                End With

                End Sub





                share|improve this answer













                In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.



                Sub FFF()

                Dim dt1 As Date, dt2 As Date
                Dim sDate1$, sDate2

                '// Get dates
                dt1 = Now - 29: dt2 = Now - 1

                '// Format dates
                sDate1 = Format(dt1, "MM/yy/yyyy")
                sDate2 = Format(dt2, "MM/yy/yyyy")

                With Sheets("Main").Range("A1").CurrentRegion
                .AutoFilter
                .AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2

                End With

                End Sub






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 5 at 19:32









                JohnyLJohnyL

                3,7231924




                3,7231924

























                    -1














                    Simple code to filter the dates for your ranges, modify to meet your needs...



                    With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
                    .AutoFilter
                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
                    'Do something

                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
                    'Do something

                    End With





                    share|improve this answer





















                    • 1





                      1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                      – JohnyL
                      Jan 5 at 19:33











                    • @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                      – GMalc
                      Jan 6 at 19:20
















                    -1














                    Simple code to filter the dates for your ranges, modify to meet your needs...



                    With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
                    .AutoFilter
                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
                    'Do something

                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
                    'Do something

                    End With





                    share|improve this answer





















                    • 1





                      1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                      – JohnyL
                      Jan 5 at 19:33











                    • @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                      – GMalc
                      Jan 6 at 19:20














                    -1












                    -1








                    -1







                    Simple code to filter the dates for your ranges, modify to meet your needs...



                    With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
                    .AutoFilter
                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
                    'Do something

                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
                    'Do something

                    End With





                    share|improve this answer















                    Simple code to filter the dates for your ranges, modify to meet your needs...



                    With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
                    .AutoFilter
                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
                    'Do something

                    .AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
                    'Do something

                    End With






                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited Jan 6 at 19:27

























                    answered Jan 2 at 14:33









                    GMalcGMalc

                    1,1511411




                    1,1511411








                    • 1





                      1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                      – JohnyL
                      Jan 5 at 19:33











                    • @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                      – GMalc
                      Jan 6 at 19:20














                    • 1





                      1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                      – JohnyL
                      Jan 5 at 19:33











                    • @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                      – GMalc
                      Jan 6 at 19:20








                    1




                    1





                    1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                    – JohnyL
                    Jan 5 at 19:33





                    1) Range doesn't have AutoFilterMode property. 2) Autofilter recognizes only U.S. date format. Did you test your code?

                    – JohnyL
                    Jan 5 at 19:33













                    @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                    – GMalc
                    Jan 6 at 19:20





                    @JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the AutoFilterMode to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.

                    – GMalc
                    Jan 6 at 19:20


















                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005579%2ffilter-between-2-date-ranges%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    qJBgSEVvqYgw042BnvMhxB8ECeKPK3wA KTVlmyFKvPpd8HgvIVODZ,Eubgb0tZLf2zuGJFOq5nanvOBnfLwqA
                    H KvBHdhCY A6,IYdEgenzxm ncbWRNj8xxiHaZQc,AM k2EfGJ9rtCrjp 9Fc

                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas