Remember Me?

#1
June 20th 11, 08:23 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 202

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

#2
June 20th 11, 09:26 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 202

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))))

#3
June 20th 11, 10:55 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 38

On Jun 20, 9:26*am, Seanie wrote:
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

#4
June 20th 11, 07:32 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 202

^^^^ 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)

#5
June 22nd 11, 07:19 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2010 Posts: 1,522

On Jun 20, 1:32*pm, Seanie wrote:
^^^^ 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."

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post The Caterer Excel Discussion (Misc queries) 1 November 29th 09 08:08 AM Jeremy Excel Discussion (Misc queries) 3 September 23rd 08 09:03 PM Chief 711 Excel Worksheet Functions 5 May 13th 08 04:34 PM Kevin B Excel Discussion (Misc queries) 4 October 18th 07 05:05 PM marie Excel Worksheet Functions 7 December 7th 05 02:36 PM

All times are GMT +1. The time now is 12:02 PM.