You are correct Bob, but formula returns 3 but when I count up manually
it should be 6. Not sure whats the problem

Bob Phillips wrote:
As I read your requirement, it counts the item if it clocks in before or on
6:00 pm, and clocks out after or on 7:00 pm of the day in question.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sean" wrote in message
oups.com...
Thanks Bob,

Is my understanding of your formula correct in that-

Return 1 for all Clock In times that are Greater or Equal to (7:00pm -
1 hour)
Return 1 for all Clock Out times that are Greater or Equal to (7:00pm)

Taking AC48= 7:00pm

Then Multiply

But would that ignore-:
a) Those that have a Clock In before 6:00pm, that has a clock Out after
7:00pm, thus they are working for the 7:00pm hour?
b) Those that have a Clock Out time at some stage between 6:00pm and
6:59pm



Bob Phillips wrote:
This works for me


=SUMPRODUCT(--(C$8+$AC48-TIME(1,0,0)=In_Time),--(Out_time=(C$8+$AC48)))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sean" wrote in message
oups.com...
I got the following formula from this Newsgroup and re-did to fit, but
its not quite right. I am trying to total the number of employees
clocked in for each hour of the day, from values I have imported from
a
Database



=SUMPRODUCT((In_Time<=($AC48+C$8))*1,(Out_Time=(( $AC48+C$8)+TIME(0,60,0)))*
1)

In_Time & Out_Time = a range name of Clock in/out times formatted in
h:mm AM/PM
AC48 = the value 7:00pm
C$8 = a date, in this example 30/10/06

My value imported from the database show in Excel as dd/mm/yy hh:mm
AM/PM

Thus the formula above should total the number of entries clocked
between 6:00pm and 7:00pm for the 30/10/06. My problem is that it
returns 0 and that is not correct. I have values for other times and
they seem to be correct, so there must be something wrong in the
formula as it appears inconsistent

Any help appreciated