But the defined time is a range between 6:00pm and 7:00pm, how does the
formula deal with values in between, in that employees that clock out
between 6:00pm and 7:00pm would be classified as working in that time
slot. I wish to capture not just those that are still working at 7:00pm
but those that worked any part of 6:00pm to 7:00pm time slot


Bob Phillips wrote:
It is just checking if the in cell value is not later than the defined time
less one hour, and the out cell value is not earlier than the defined time.

--

HTH

Bob Phillips

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

"Sean" wrote in message
ups.com...
When I enter the following formula I do get 6 returned for the 7:00pm
hour, but I don't fully understand the logic of the formula, thus I'm
unsure if it is 'truly correct'


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


Sean wrote:
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