Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
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 |