Filter time range












-1















We are trying to get a report as to how many tickets where logged out of hours.



Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.



I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month



Can anyone help



Thanks










share|improve this question


















  • 1





    Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

    – Worthwelle
    Dec 19 '18 at 22:23











  • A detail sample will more helpful.

    – Lee
    Dec 24 '18 at 9:08
















-1















We are trying to get a report as to how many tickets where logged out of hours.



Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.



I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month



Can anyone help



Thanks










share|improve this question


















  • 1





    Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

    – Worthwelle
    Dec 19 '18 at 22:23











  • A detail sample will more helpful.

    – Lee
    Dec 24 '18 at 9:08














-1












-1








-1








We are trying to get a report as to how many tickets where logged out of hours.



Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.



I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month



Can anyone help



Thanks










share|improve this question














We are trying to get a report as to how many tickets where logged out of hours.



Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.



I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month



Can anyone help



Thanks







microsoft-excel time






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Dec 19 '18 at 21:30









JamesJames

11




11








  • 1





    Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

    – Worthwelle
    Dec 19 '18 at 22:23











  • A detail sample will more helpful.

    – Lee
    Dec 24 '18 at 9:08














  • 1





    Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

    – Worthwelle
    Dec 19 '18 at 22:23











  • A detail sample will more helpful.

    – Lee
    Dec 24 '18 at 9:08








1




1





Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

– Worthwelle
Dec 19 '18 at 22:23





Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.

– Worthwelle
Dec 19 '18 at 22:23













A detail sample will more helpful.

– Lee
Dec 24 '18 at 9:08





A detail sample will more helpful.

– Lee
Dec 24 '18 at 9:08










1 Answer
1






active

oldest

votes


















0














I would like to suggest Two different methods will help you to fix the issue.



Method 1:



This needs a Helper Column and then Auto Filter.



enter image description here




  1. I'm assuming that you have Dates in Range A4:A13.

  2. Fill Cell A1 with Start Time & Cell A2 with End Time.


(check the Time criteria in Green color).





  1. Enter this Formula in Cell B4 & fill it Down.



     =IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")


  2. Apply Auto Filter to required Range (A4:B13) & Filter Good Time.



Method 2:



This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.



enter image description here



Note:





  1. Source Data Range is A4:A13.


  2. Criteria Cells are A1 & A2.


  3. Counter is in Cell C1.


Write this Formula in Cell C1.



=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))


Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.



{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}


You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    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%2fsuperuser.com%2fquestions%2f1386053%2ffilter-time-range%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I would like to suggest Two different methods will help you to fix the issue.



    Method 1:



    This needs a Helper Column and then Auto Filter.



    enter image description here




    1. I'm assuming that you have Dates in Range A4:A13.

    2. Fill Cell A1 with Start Time & Cell A2 with End Time.


    (check the Time criteria in Green color).





    1. Enter this Formula in Cell B4 & fill it Down.



       =IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")


    2. Apply Auto Filter to required Range (A4:B13) & Filter Good Time.



    Method 2:



    This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.



    enter image description here



    Note:





    1. Source Data Range is A4:A13.


    2. Criteria Cells are A1 & A2.


    3. Counter is in Cell C1.


    Write this Formula in Cell C1.



    =SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))


    Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.



    {=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}


    You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.






    share|improve this answer




























      0














      I would like to suggest Two different methods will help you to fix the issue.



      Method 1:



      This needs a Helper Column and then Auto Filter.



      enter image description here




      1. I'm assuming that you have Dates in Range A4:A13.

      2. Fill Cell A1 with Start Time & Cell A2 with End Time.


      (check the Time criteria in Green color).





      1. Enter this Formula in Cell B4 & fill it Down.



         =IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")


      2. Apply Auto Filter to required Range (A4:B13) & Filter Good Time.



      Method 2:



      This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.



      enter image description here



      Note:





      1. Source Data Range is A4:A13.


      2. Criteria Cells are A1 & A2.


      3. Counter is in Cell C1.


      Write this Formula in Cell C1.



      =SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))


      Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.



      {=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}


      You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.






      share|improve this answer


























        0












        0








        0







        I would like to suggest Two different methods will help you to fix the issue.



        Method 1:



        This needs a Helper Column and then Auto Filter.



        enter image description here




        1. I'm assuming that you have Dates in Range A4:A13.

        2. Fill Cell A1 with Start Time & Cell A2 with End Time.


        (check the Time criteria in Green color).





        1. Enter this Formula in Cell B4 & fill it Down.



           =IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")


        2. Apply Auto Filter to required Range (A4:B13) & Filter Good Time.



        Method 2:



        This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.



        enter image description here



        Note:





        1. Source Data Range is A4:A13.


        2. Criteria Cells are A1 & A2.


        3. Counter is in Cell C1.


        Write this Formula in Cell C1.



        =SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))


        Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.



        {=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}


        You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.






        share|improve this answer













        I would like to suggest Two different methods will help you to fix the issue.



        Method 1:



        This needs a Helper Column and then Auto Filter.



        enter image description here




        1. I'm assuming that you have Dates in Range A4:A13.

        2. Fill Cell A1 with Start Time & Cell A2 with End Time.


        (check the Time criteria in Green color).





        1. Enter this Formula in Cell B4 & fill it Down.



           =IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")


        2. Apply Auto Filter to required Range (A4:B13) & Filter Good Time.



        Method 2:



        This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.



        enter image description here



        Note:





        1. Source Data Range is A4:A13.


        2. Criteria Cells are A1 & A2.


        3. Counter is in Cell C1.


        Write this Formula in Cell C1.



        =SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))


        Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.



        {=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}


        You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 20 '18 at 8:03









        Rajesh SRajesh S

        1




        1






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • 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%2fsuperuser.com%2fquestions%2f1386053%2ffilter-time-range%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







            Popular posts from this blog

            Plaza Victoria

            Puebla de Zaragoza

            Musa