Report on Number of Employees Clocked in

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am looking for advise on a Report I wish to create that will show me total
number of employees that are clocked in each hour. It seems simple but I'm
usure of how to structure it.

I have pulled from a database information per employee per day (for a 7 day
period). This shows the following

StartTime, Break1Start, Break1End, Break2Start, Break2End, OutTime

Obviously not all employees are always clocked in i.e. days off etc, nor do
all employees take two breaks in a day etc.

For my Report I have hours running from 7:00am to 6:00am (23 hours) in
Column A, in Column C I am hoping (somehow) to show the number of Employees
that are clocked in for this particular hour and day. Day 2 will be shown in
Column D etc etc.

My Database that I pull the clock info is dynamic for dates and staff
clocked in.

Somehow I need to do some sort of "Lookup" to check if each employee has a
clock-in for that day, then see if its StartTime is before the particular
hour I'm looking to check and its EndTime is after the particular hour in
Column A, then total the number of employees that this applies to for a
particular day and hour. Then do this for each day etc.

Could anyone advise

Thanks
 
I think that the best thing is to create group SQL query that extract
the data group by hour. this way
(if your database is oracle it would be something like this:
select to_char(StartTime,'HH24') as MY_TIME, count(*) as MY_COUNT from

Employees
where ....
group by to_char(StartTime,'HH24')

if you need to create this reports regulary, and run it from excel, i
would recomend this software that i found (It is so nice that I keep
recomending this to everybody), http://urisoft.netfirms.com/
 
Another way of approaching this is to generate 6 records for each
employee, and it might be better to have dates and times rather than
just times. Three of these records would contain the START date/times
in column B (i.e. start-time, break1_end and break2_end) and in column
C an indicator such as "S". The other 3 records would contain the END
date/times (Break1_start, Break2_start and EndTime) and column C would
contain an indicator such as "E".

The six sets of records should be merged together and sorted by column
B (date/time). Any records with missing date/times should drop to the
bottom and can be deleted. Assume this data starts in row 2 - you can
enter zero in D1 and in D2 the following formula:

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

and copy this down. This increments or decrements a counter down the
column depending on whether the time in B is for someone starting or
ending, and so this will give you in column D the number of
simultaneous employees working at any point in time (referenced to the
value in column B). You can thus use this with MAX(IF( ) ) to get the
maximum within any time period you specify.

Hope you can make use of this.

Pete
 
Back
Top