Home 
Search 
Today's Posts 
#1




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: 1011am; 1112pm; 121pm 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 1112pm 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




Add Time Q
Progress of sorts, the formula below will show me the number of
instances that Clocks occurred between 11am12pm, 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




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 11am12pm, 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 datetime values will also be a datetime value. So time diferences will be a fraction of a day. The minutes of the hour for any one datetime value is obtained by MINUTE(), similarly the hour of the day for any one datetime 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 =B3A3 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




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:0010:00 = 30mins labour hours worked 10:0111:00 = 90mins labour hours worked 11:0112:00 = 150min labour hours worked 12:0113:00 = 180min labour hours worked 13:0114:00 = 180min labour hours worked 14:0115:00 = 145min labour hours worked 15:0116:00 = 30min labour hours worked I have 300+ rows of data, so can't do it manually (not efficiently anyway) 
#5




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:0010:00 = 30mins labour hours worked 10:0111:00 = 90mins labour hours worked 11:0112:00 = 150min labour hours worked 12:0113:00 = 180min labour hours worked 13:0114:00 = 180min labour hours worked 14:0115:00 = 145min labour hours worked 15:0116: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 
Display Modes  


Similar Threads  
Thread  Forum  
Time calculations for Scheduled Time vs. Actual Time Worked  Excel Discussion (Misc queries)  
straight time, time and a half, and double time  Excel Discussion (Misc queries)  
Calculate Ending time using Start Time and Elapsed Time  Excel Worksheet Functions  
Converting date/time serial values to cumulative time totals...  Excel Discussion (Misc queries)  
Calculating days & time left from start date/time to end date/time  Excel Worksheet Functions 