Conditional time problem with TIME(0,0,0))

  • Thread starter Thread starter josie
  • Start date Start date
J

josie

I'm using the following formula to count calls within 4-hr tim
periods:

=SUM((Tues_CallRecords!$D$3:$D$5000>TIME(0,0,0))*(Tues_CallRecords!$D$3:$D$5000<TIME(4,0,0))*1)

But, it fails to count the calls that came in right at 12am (0,0,0)
However, when I change it to >=TIME(0,0,0), it overstates the total b
1,806 calls.

Apparently, Excel thinks 1,806 calls came in at =TIME(0,0,0), when onl
1 came in. The time cells do not contain info by the second, s
evaluating based on something like >=TIME(0,0,1) isn't possible.

What's wrong, and how can I fix it
 
Hi,

try:

=SUM(IF((Tues_CallRecords!$D$3:$D$5000
=TIME(0,0,0))*(Tues_CallRecords!$D$3:$D$5000<=TIME(4,0,0)),1,0))
Click to expand...

To be entered using CTRL+SHIFT+ENTER

Hope this helps!
 
Back
Top