#1   Report Post  
Old 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
Default Add Time Q

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   Report Post  
Old 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
Default Add Time Q

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   Report Post  
Old 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
Default Add Time Q

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   Report Post  
Old 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
Default Add Time Q

^^^^ 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   Report Post  
Old 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
Default Add Time Q

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."


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time calculations for Scheduled Time vs. Actual Time Worked The Caterer Excel Discussion (Misc queries) 1 November 29th 09 08:08 AM
straight time, time and a half, and double time Jeremy Excel Discussion (Misc queries) 3 September 23rd 08 09:03 PM
Calculate Ending time using Start Time and Elapsed Time Chief 711 Excel Worksheet Functions 5 May 13th 08 04:34 PM
Converting date/time serial values to cumulative time totals... Kevin B Excel Discussion (Misc queries) 4 October 18th 07 05:05 PM
Calculating days & time left from start date/time to end date/time marie Excel Worksheet Functions 7 December 7th 05 02:36 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017