Count occurences of time
 

Hi

Could you please recommend a formula to count the number of time a person is
working in the office each hour of the day during the week? Given work day is
between 8:00am and 6:00pm and the table only contain one week time card
information

sample data
Start time End Time Person
9:00am 11:30 am A
8:30 am 1:00 pm B
10:00am 2:00 pm A
9:45 am 11:00am A
8:30 am 10:00 pm A

eg in the above data the summary for Person A would be
Hour In the office
8:00 1
9:00 3
10:00 4
11:00 3
and so on

Any help would be appriecated

LoyalHarp

Count occurences of time
 

=SUMPRODUCT(--($C$1:$C$6="A"),--($A$1:$A$6=--"08:00"),--($A$1:$A$6<--"09:00"))

and

=SUMPRODUCT(--($C$1:$C$6="A"),--($A$1:$A$6=--"09:00"),--($A$1:$A$6<=--"10:00"))

--
__________________________________
HTH

Bob

"LoyalHarp" wrote in message
...
Hi

Could you please recommend a formula to count the number of time a person
is
working in the office each hour of the day during the week? Given work day
is
between 8:00am and 6:00pm and the table only contain one week time card
information

sample data
Start time End Time Person
9:00am 11:30 am A
8:30 am 1:00 pm B
10:00am 2:00 pm A
9:45 am 11:00am A
8:30 am 10:00 pm A

eg in the above data the summary for Person A would be
Hour In the office
8:00 1
9:00 3
10:00 4
11:00 3
and so on

Any help would be appriecated

LoyalHarp