Complicated Formula - I think

[Sean]

I am looking to Count the number of Employees that are working for each
Hour of the Day.

My Database will show an entry for each Employee per Day, with Start
Time and End Time (Col B = date; Col C = Start Time for that Day; Col D
= End Time for that Day). This Database table is dynamic

On my Presentation Sheet of have the Business date appearing in Col D
to J and the Time split in hours in Col A, thus 9:00am; 10:00 etc etc

I'm looking for some formula that will achieve a Count of the number of
employees for each Day that are "clocked in" for each Hour. The formula
would then have to Reference Col C and D in my Database.

I have Dynamic Named Ranges set for the Database but not sure how I
count for a Particular Hour as this has to take into consideration if
'the employee has a Clock out time later than the hour I am looking to
Count for'

Any assistance would be great

 

[Pete_UK]

One way of doing this (in a separate sheet) is to join the date and
time columns together so that you have a start date/time and a finish
date/time for each employee. Introduce a new column between the start
and finish and fill this with a value like "S". Then copy (move) all
the finish date/times to the same column as the start date/times, but
immediately below the start date/times, and you can fill below the "S"
values a value like "F". Then sort all the data using the date/time
column as the sort field.

It is then quite easy to introduce a formula which increments the
number above if the value in the extra field is "S" and decrements it
otherwise. Ifyour date/times are in column B and the S/F values in
column C, then this formula in D2 will achieve this:

=IF(C2="S", D1+1,D1-1)

assuming you have headings on row 1. Copy this formula down and the
numbers shown in column D will be the number of employees working at
the date/time shown in column B.

Hope this is what you want.

Pete

 

[Sean]

Thanks Pete for your response, but I'm a bit lost at this bit

Then copy (move) all the finish date/times to the same column as the
start date/times, but immediately below the start date/times, and you
can fill below the "S" values a value like "F". Then sort all the data
using the date/time column as the sort field.

Do you mean by way of example in
Row2 Col B = 16/11/06 09:00 ; Col C = S
Row3 Col B = 16/11/06 17:00 ; Col C = F

 

[Pete_UK]

Yes, essentially you are duplicating the records - one is for starting
and the other is for finishing (hence my suggestion of S and F).

This approach can be used for room/building occupancy, for tracking
numbers of simultaneous telephone calls etc.

Hope this helps.