Add Time Q

[Seanie]

I am trying to add up the hours for all employees that are working
over each hour of the day, thus I am looking as to how I can get the
total hours worked each hour eg: 10-11am; 11-12pm; 12-1pm etc etc

My data is listed in a database format

Col A= Employee Name
Col B= Date the employee in Col A worked
Col C= Clock in Time
Col D= Clock out time

In a separate sheet I have the following
C8= Date I want to add up the hours for
AC36 to AC59= The Time parts I wish to seek the total hours for eg
AC41= 12:00pm

How can I express - to add up all hours worked between 11-12pm for all
employees on a particular date? As the data I have is only Start and
End times, I'm confused as to how I can get the total hours worked
each hour

Any help would be appreciated

 

[Seanie]

Progress of sorts, the formula below will show me the number of
instances that Clocks occurred between 11am-12pm, so I can equate
1=60mins, but it can't handle partial hours, so if some one was
clocked in at 11:15am, it should return 45mins, but doesn't it returns
1 (60 mins)

=SUMPRODUCT(--(C$8+$AC41>=In_Time),--(Out_Time>=(C$8+$AC41-
TIME(0,59,59))))

 

[alanglloyd]

Progress of sorts, the formula below will show me the number of
instances that Clocks occurred between 11am-12pm, so I can equate
1=60mins, but it can't handle partial hours, so if some one was
clocked in at 11:15am, it should return 45mins, but doesn't it returns
1 (60 mins)

Dates & Times are essentially handled by Excel as whole numbers for
the day (counted from a datum about 1900) and decimals for the
fraction of the day.

Differences between date-time values will also be a date-time value.
So time diferences will be a fraction of a day.

The minutes of the hour for any one date-time value is obtained by
MINUTE(), similarly the hour of the day for any one date-time value is
obtained by HOUR(). Note that these values are _of the hour_ or _of
the day, NOT the elapsed time unless they are the differences of two
values in one hour (for MINUTE()), or of two values in one day (for
HOUR()).

To demonstrate put the following in row 3 of a spreadsheet :

A3 11:15
B3 12:00
C3 =B3-A3
D3 =MINUTE(C3)

then put

F2 =5.8
G2 Hourly Rate
E3 =D3+(HOUR(C3)*60)
F3 =($F$2*E3)/60

A3 is start time (note the colon)
B3 is finish time
C3 is the time difference
D3 is the minute of time difference (of the hour of the integer of the
time difference)
E3 is the total minutes difference of the time difference

.... and what you might pay ...

F2 is hourly rate
F3 is pay amount

Then you can play & vary the times & understand how to handle time
difference values.

Alan Lloyd

 

[Seanie]

^^^^ Thanks for your reply, but not really what I'm looking for, which
is a formula/function that can add up hours within each Hour segment
for a day, eg

The following clocks
Emp#1 In=09:30 Out=14:30
Emp#2 In=10:30 Out=14:55
Emp#3 In= 11:30 Out=15:30

So for the following Hour segments the formula/function should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

I have 300+ rows of data, so can't do it manually (not efficiently
anyway)

 

[Don Guillett]

^^^^ Thanks for your reply, but not really what I'm looking for, which
is a formula/function that can add up hours within each Hour segment
for a day, eg

The following clocks
Emp#1 In=09:30 Out=14:30
Emp#2 In=10:30 Out=14:55
Emp#3 In= 11:30 Out=15:30

So for the following Hour segments the formula/function should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

I have 300+ rows of data, so can't do it manually (not efficiently
anyway)

"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."