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

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

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

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

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

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

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.
--
David Biddulph

"Sean" wrote in message
oups.com...
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