Calculate Total Employee Hours Worked By The Hour

[mitchande21]

I am having trouble finding an efficient way to manage this. What I am looking to do is find a way to calculate the total number of hours worked by employees for each hour during the day. There is an example of how the schedule exports to excel in a table below.

I want to find out how many hours are being worked from 7:00 to 8:00, 8:00 to 9:00, etc. By calculating manually, Employee 1 works for 1 hour during the 7:00 hour and employee 2 works for 15 minutes during the 7:00 hour so the total number of hours worked for the 7:00 hour is 1.25. Total hours worked for the 8 o'clock hour is 1.75. etc. How could I make excel calculate this for each hour throughout the day while taking into account when employees are off as well (ex. Employee 2 only works for 15 minutes during the 1:00 hour)

Thanks for any help here!
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Time In
[/TD]
[TD]Time Out
[/TD]
[TD]Shift Length
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]7:00 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]7:45 AM
[/TD]
[TD]1:15 PM
[/TD]
[TD]5.5
[/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]8:30 AM
[/TD]
[TD]2:00 PM
[/TD]
[TD]5.5
[/TD]
[/TR]
[TR]
[TD]Employee 4
[/TD]
[TD]10:00 AM
[/TD]
[TD]2:00 PM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Employee 5
[/TD]
[TD]10:00 AM
[/TD]
[TD]3:00 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Employee 6
[/TD]
[TD]12:00 PM
[/TD]
[TD]4:15 PM
[/TD]
[TD]4.25
[/TD]
[/TR]
[TR]
[TD]Employee 7
[/TD]
[TD]12:15 PM
[/TD]
[TD]6:00 PM
[/TD]
[TD]5.75
[/TD]
[/TR]
[TR]
[TD]Employee 8
[/TD]
[TD]1:00 PM
[/TD]
[TD]7:00 PM
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Employee 9
[/TD]
[TD]3:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JPARKHURST]

Thanks for any help here!
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Shift Length[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]7:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]7:45 AM[/TD]
[TD]1:15 PM[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]8:30 AM[/TD]
[TD]2:00 PM[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]10:00 AM[/TD]
[TD]2:00 PM[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Employee 5[/TD]
[TD]10:00 AM[/TD]
[TD]3:00 PM[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Employee 6[/TD]
[TD]12:00 PM[/TD]
[TD]4:15 PM[/TD]
[TD]4.25[/TD]
[/TR]
[TR]
[TD]Employee 7[/TD]
[TD]12:15 PM[/TD]
[TD]6:00 PM[/TD]
[TD]5.75[/TD]
[/TR]
[TR]
[TD]Employee 8[/TD]
[TD]1:00 PM[/TD]
[TD]7:00 PM[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Employee 9[/TD]
[TD]3:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Sounds like you are still beginning, so I figured some structure questions may help. Can you modify the table or create another worksheet?

You could run a pivot table, which may be your best answer, but it would require some structure changes as well to include your time blocks to display. I could also easily see a 24 hour calendar, grabbing values by time slots, would that work? You could do so with a macro or with cell formulas.

For example, I have a sheet which reports capacity for production during a specified time. Here is the formula in case you want to look at it. I've got a thing and will be back later.

=SUMIFS(Working_Dispatch!$H:$H,Working_Dispatch!$A:$A,">="&$C$26,Working_Dispatch!$A:$A,"<="&$D$26,Working_Dispatch!$B:$B,C7)

You would put this in a time block area, and compare it to each employee.

Alternately, you can also build a calendar for the year (not as hard as it seems), or do it by month, or financial month, and layout employee schedule like that. I like to build some of these 'larger' tables

 

[mitchande21]

You're right, I am pretty new to excel and usually just fumble my way through until I find a way to get what I need to work. The table can be modified and another worksheet can be used. I have got to the point where I am able to tell how many employees are working during any given hour by using something similar to the example below:

​

	A	B	C	D	E	F
Employee	Start	End	From
Employee1	To
Employee2	Sumproduct
Employee3	Countifs
Employee4
Employee5
Employee6

<colgroup style="width: auto;"><col style="width: auto;"><col style="width: auto;"><col style="width: auto;"><col style="width: auto;"><col style="width: auto;"><col style="width: auto;"><col style="width: auto;"></colgroup><thead style="width: auto;">
</thead><tbody style="width: auto;">
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10:00 AM[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4:00 PM[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9BBB59]#9BBB59[/URL] , align: right"]11:00 AM[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0504D]#C0504D[/URL] , align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9BBB59]#9BBB59[/URL] , align: right"]8:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9BBB59]#9BBB59[/URL] , align: right"]10:00 AM[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F79646]#F79646[/URL] , align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]9:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]6:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10:30 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]7:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]11:30 AM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 0"]
<tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD]Worksheet Formulas[TABLE="width: 1067"]
<thead style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMPRODUCT(--(B2:B7<F2),--(C2:C7>F1))[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=COUNTIFS(B2:B7,"<"&F2,C2:C7,">"&F1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

​

​

However, I am still coming up blank on how to calculate the amount of time worked during every hour since the formula I'm using gives the same value to somebody working 60 minutes during the hour as somebody who works 1 minute during the hour. Is there a way I could get it to calculate as 61 minutes for the hour?

Thanks for the help! I feel like I'm getting on the right track; it's just a bit above my level of understanding at the moment, but I'm learning!

 

[JPARKHURST]

Still stuck on this? Any way we can use a macro? I find it easier to manipulate data outside of a single cell reference. If not, we will get through it.

basically, just thinking out loud here in some psuedo code

if (TimeIn mod 60) <>0
'there is a remainder
TimeWorked = TimeWorked+Minutes(TimeIn)
Else
TimeWorked = TimeWorked+1

let me know where you are at, it's been a long time since i looked, at this, very sorry!