Autofilter Between 2 Times

  • Hi, i have a nice little table with lots of colums on informtion using an auto filter... my issue is i cant seem to get the auto filter to play nicly with the time column...



    08:42:20 <<< that is how the time is formatted hh:mm:ss


    now what i use a custom auto filter and try and filter all records from after 19:00:00 and before 07:00:00 it never brings anything up its blank even though i know there are records from in between these times..


    any ideas...

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    Quote from ByTheCringe2

    You mean AFTER 07:00:00 and BEFORE 19:00:00. Or you use
    BEFORE 07:00:00 OR AFTER 19:00:00.



    in the autofiler under custom there is no options for BEFORE and AFTER
    the options im using is


    "is greater than or equal to" and "is less than"

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    Column C cells are not times, but in text format. Attached is the same workbook (less vba code to save space) with column C text converted to Excel times with a filter before 8am (there weren't any pre 7am calls) and after 7pm in place.
    p45cal

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    how do i convert the data into time format? because im pasting the data from elseware then correcting all the data, dont know if you have seen the macros iv wrote in there....

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    Don't understand why that should stop it working? Alphabetic fields get selected by alpha sorting, don't they, so I would have assumed numeric text would also be selectable.

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    I did it manually by putting this formula in cell N4:
    =TIMEVALUE(C4)
    and filling down, then copy/paste special back to column C, then formatting that column to time.
    However a bit of code can do it in situ:

    Code
    Sub blah()
    For Each cll In Range(Range("C4"), Range("C4").End(xlDown))
    cll.Value = TimeValue(cll.Value)
    Next cll
    Range(Range("C4"), Range("C4").End(xlDown)).NumberFormat = "h:mm:ss;@"
    End Sub

    p45cal

  • Re: Autofilter To Filter Time Greater &amp; Less Than


    Quote from milkshake

    how do i convert the data into time format? because im pasting the data from elseware then correcting all the data, dont know if you have seen the macros iv wrote in there....


    I see that you start with some string in C4 (and down), you apply the formula
    [bfn]--MID(C4, FIND("":"",C4)-2,8)[/bfn] in column O which actually seems to give valid results (I'd have preferred to see [bfn]timevalue(MID(C4, FIND("":"",C4)-2,8))[/bfn]) but you then proceed to convert it to text with [bfn]TEXT(O4,"hh:mm:ss")[/bfn]. I don't think that last step is required (what's more, it could be what's screwing it up). Try copy/pastespecial values back into column C directly from column O and then format the whole column as Time with:

    Code
    Range(Range("C4"), Range("C4").End(xlDown)).NumberFormat = "h:mm:ss;@"
    'or
    Range("C4:C" & lastrow).NumberFormat = "h:mm:ss;@"

    I'd like to hope that your filter would work properly then.
    p45cal





    [bfn][/bfn]

Participate now!

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