How to use application.countifs to count a time interval like 7:00AM to & 7:00PM

[minmark]

Dear Gents,

I would like use application.countif to count people numbers of visit time between 7:00PM to 7:00PM
but times seems to a circle....how to define upper and lower limit and make this calculation happen?


Thanks.

 

[nemmi69]

do you have a sample of your code

 

[minmark]

Hi, nemmi69
Thanks for your attention.
Codes are following, it works now but it seems too long, does any expression can calculate <7000AM >1900 in one condition?
Thanks.

Set rng = rng.Offset(1, 0)
    With Sheet1
        For i = 0 To Day(Now()) - 1
        Sheet7.Cells(rng.Row, rng.Column + i).Value = Application.CountIfs(.Range(.Cells(10, VD), .Cells(1010, VD)), i + 1, Range(.Cells(10, PtN), .Cells(1010, PtN)), "<>""", Range(.Cells(10, Vt), .Cells(1010, Vt)), "<07:00", Range(.Cells(10, PtS), .Cells(1010, PtS)), "OUT") _
        + Application.CountIfs(.Range(.Cells(10, VD), .Cells(1010, VD)), i + 1, Range(.Cells(10, PtN), .Cells(1010, PtN)), "<>""", Range(.Cells(10, Vt), .Cells(1010, Vt)), ">19:00", Range(.Cells(10, PtS), .Cells(1010, PtS)), "OUT")
        Next i
    End With

 

[nemmi69]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Time [/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TimeValue[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"07:00:00"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]And[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Time [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TimeValue[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"19:00:00"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            'do stuff[/FONT][/COLOR]</code>[COLOR=#303336][FONT=inherit]        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If
[/FONT][/COLOR]

 

[minmark]

Thanks~