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

#### minmark

##### New Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### nemmi69

##### Well-known Member
do you have a sample of your code

#### minmark

##### New Member
Hi, nemmi69
Codes are following, it works now but it seems too long, does any expression can calculate <7000AM >1900 in one condition?
Thanks.
Code:
``````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

##### Well-known Member
Code:
``````<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]``````

Thanks~

Replies
2
Views
325
Replies
6
Views
818
Replies
4
Views
897
Replies
0
Views
890
Replies
2
Views
375

1,171,648
Messages
5,876,652
Members
433,205
Latest member
jabin1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back