|
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
Not a problem if you use
=TIME(19,0,0) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "David Biddulph" wrote in message ... 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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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 |
Number of Staff per Hour Clocked in
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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
=--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
Afraid not, Bob even further away
Bob Phillips wrote: =--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
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" wrote in message ups.com... Afraid not, Bob even further away Bob Phillips wrote: =--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
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 wrote: 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" wrote in message ups.com... Afraid not, Bob even further away Bob Phillips wrote: =--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
Go for it Sean!
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sean" wrote in message ups.com... 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 wrote: 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" wrote in message ups.com... Afraid not, Bob even further away Bob Phillips wrote: =--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Number of Staff per Hour Clocked in
Bob, I've sent it through, hope its to the correct address
Bob Phillips wrote: Go for it Sean! -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sean" wrote in message ups.com... 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 wrote: 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" wrote in message ups.com... Afraid not, Bob even further away Bob Phillips wrote: =--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" wrote in message oups.com... 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 wrote: 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" wrote in message ups.com... 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 |
| All times are GMT +1. The time now is 08:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com