Number of Staff per Hour Clocked in

[Sean]

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

 

[Bob Phillips]

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]

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]

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]

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

 

[Sean]

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))))

 

[David Biddulph]

You may need to be careful if there are clockings exactly on the 7 pm mark,
as that isn't a number which Excel can store exactly in binary (19/24 can't
be represented exactly in binary, nor in decimal). 6 pm will be OK as 18/24
is 0.75 which does have an exact binary representation.

 

[Bob Phillips]

Not a problem if you use

=TIME(19,0,0)

--

HTH

Bob Phillips

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

 

[Bob Phillips]

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]

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]

Certainly didn't get that from the original description

=SUMPRODUCT(((C$8+$A8-TIME(1,0,0)>=In_Time)*(Out_Time>=(C$8+$A8)))+

((C$8+$A8-TIME(1,0,0)>=In_Time)*(Out_Time<(C$8+$A8)))+

((C$8+$A8-TIME(1,0,0)<In_Time)*(Out_Time>=(C$8+$A8))))

--

HTH

Bob Phillips

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

 

[Sean]

Bob, thanks for your perseverance, this formula appears to be returning
the total number of clocks for all of the date that is within C8,
regardless if the employee has clocked out before 6:00pm (and left) or
hasn't a Clock In before 7:00pm (not started work yet)

 

[Bob Phillips]

=--OR(AND(In_Time>=$A$8+$AC$48-TIME(1,0,0),In_Time<=$A$8+$AC$48),
AND(Out_Time>=$A$8+$AC$48-TIME(1,0,0),Out_Time<=$A$8+$AC$48),
AND(In_Time<=$A$8+$AC$48-TIME(1,0,0),Out_Time>=$A$8+$AC$48))

--

HTH

Bob Phillips

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

 

[Sean]

Afraid not, Bob even further away

=--OR(AND(In_Time>=$A$8+$AC$48-TIME(1,0,0),In_Time<=$A$8+$AC$48),
AND(Out_Time>=$A$8+$AC$48-TIME(1,0,0),Out_Time<=$A$8+$AC$48),
AND(In_Time<=$A$8+$AC$48-TIME(1,0,0),Out_Time>=$A$8+$AC$48))

--

HTH

Bob Phillips

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

 

[Bob Phillips]

In what way, it worked for me in all values I could think of.

--

HTH

Bob Phillips

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

 

[Sean]

Bob, it just gives me an incorrect value i.e 1 but I'm expecting 6

Not really sure what the logic behind each part of the formula is so
I'm finding it hard to analyse whats wrong, I could strip the file down
and e-mail it

 

[Bob Phillips]

Go for it Sean!

--

HTH

Bob Phillips

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

 

[Sean]

Bob, I've sent it through, hope its to the correct address