Calculating Counts based on Date and Time Frame

[Brendon]

I have the following formula:

=IF(ISBLANK(A7),"",SUMPRODUCT(--(PTARRTIME<>""),--(PTARRTIME>=TIME(LEFT
(C$6,2),MID(C$6,4,2),0)),--(PTARRTIME<TIME(MID(C$6,7,2),RIGHT(C$6,2),
59)),--(PTORDDATE=$A7)))

Basically what this does is look into C6, (05:00-05:29) and calculate
the number of people seen between these times, and on the date (A7).

For some reason, it's not calculating correctly. When I do manual
counts, it gives me a different number. Am I missing something here?

Brendon

 

[Bernd P]

Hello Brendon,

The formula looks fine. Maybe you need some data validation checks on
your input data, such as conditional formats which highlight erroneous
inputs.
Example: conditional format with formula =AND(ISNUM(LEFT(C$6,2),...)

Regards,
Bernd

 

[jaf]

Hi Brendon,
One thing to keep in mind.
When dealing with time a millisecond off can cause an error.
So ,--(PTARRTIME<TIME(MID(C$6,7,2),RIGHT(C$6,2), may give a different result if you change "<" to "=<"
If PTORDDATE has a time as well as a date component you could get a similar error.
You seem to have everything else covered.

John