Calculate Number of Employees Who Were At Work Each Hour?

[FancyPegLeg]

I am trying to come up with a set of formulas to calculate how many employees were on the clock fo each hour of the day based on:

Column A: Employee Name
Column B: Employee Time Start
Column C: Number of hours Employee Worked

For instance:
Employee 1 started at 10 AM and worked 8 hours
Employee 2 started at 11 AM and worked 3 hours
And so on...

I need to calculate from this how many people were here from 10 AM to 11 AM, 11 AM to 12 PM, and so on.

Does anyone have any tips on how to do this?

Any help is greatly appreciated!

 

[Gerald Higgins]

Hi, welcome to the board.

What I would do, I think, is to build a time sheet, with names down the left hand side, followed by start times, and then worked hours, and hours of the day along the top.
Then, for each hour / person permutation, show a 1 (or something similar), to indicate that a person worked that hour, and a zero or blank if they didn't. Then you can count or sum the 1s.
You can write formulas to generate the 1s / 0s based on their start time and worked hours.

 

[FancyPegLeg]

Thanks for the response! I was thinking along those lines, but I haven't figured out what formulas to use to generate the ones and zeroes for each of the permutations. Would COUNTIF work? There are two separate variables in play (start time and hours worked) so I don't know quite how to do this. Or would it make more sense to use end time instead of hours worked?

 

[kevinb11]

I am trying to come up with a set of formulas to calculate how many employees were on the clock fo each hour of the day based on:

Column A: Employee Name
Column B: Employee Time Start
Column C: Number of hours Employee Worked

For instance:
Employee 1 started at 10 AM and worked 8 hours
Employee 2 started at 11 AM and worked 3 hours
And so on...

I need to calculate from this how many people were here from 10 AM to 11 AM, 11 AM to 12 PM, and so on.

Does anyone have any tips on how to do this?

Any help is greatly appreciated!

What about breaks(lunch)??? Is this a 24 hour operation? If not Hours of Operation

 

[FancyPegLeg]

I'm not worried about breaks. It's a 24 hour operation so there will have to be some finagling for the early morning hours to count the people from the previous day. I'm pretty sure I can figure that part out once I have a basic formula for each hour, however.

 

[Gerald Higgins]

Here's a simple version.

Let's say your first name is in cell A2
Their start time is in B2, let's say it's 1am, entered as an Excel time.
Hours worked is in C2, let's say it's 2 hours, let's say just entered as a number (not an Excel time).
Let's say the hours of the day are along the top, starting with 0:00:00 in D1, then 01:00:00 in E1, and so on.
Put this formula in D2, and copy across and down.

=IF(AND($B2<=D$1,($B2+$C2/24)>=E$1),1,0)

This will put a 1 into cols E and F, indicating those hours were worked, and a zero into all the others.

Comments - if you need to deal with less than whole hours, you'll need something more complicated.
Also, if you need to deal with shifts that go beyond midnight, you'll need something more complicated.

 

[MrVillareal]

hi try this,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Employee</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;"></td><td style=";">From</td><td style="text-align: right;background-color: #FFFF00;;">10:00 AM</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Employee1</td><td style="text-align: right;background-color: #FFFF00;;">10:00 AM</td><td style="text-align: right;background-color: #FFFF00;;">4:00 PM</td><td style="text-align: right;;"></td><td style=";">To</td><td style="text-align: right;background-color: #9BBB59;;">11:00 AM</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Employee2</td><td style="text-align: right;;">11:00 AM</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;"></td><td style=";">Sumproduct</td><td style="text-align: right;background-color: #C0504D;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Employee3</td><td style="text-align: right;background-color: #9BBB59;;">8:00 AM</td><td style="text-align: right;background-color: #9BBB59;;">10:00 AM</td><td style="text-align: right;;"></td><td style=";">Countifs</td><td style="text-align: right;background-color: #F79646;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Employee4</td><td style="text-align: right;background-color: #FFFF00;;">9:00 AM</td><td style="text-align: right;background-color: #FFFF00;;">6:00 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Employee5</td><td style="text-align: right;background-color: #FFFF00;;">10:30 AM</td><td style="text-align: right;background-color: #FFFF00;;">7:00 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Employee6</td><td style="text-align: right;;">11:30 AM</td><td style="text-align: right;;">8:00 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B2:B7<F2</font>),--(<font color="Red">C2:C7>F1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=COUNTIFS(<font color="Blue">B2:B7,"<"&F2,C2:C7,">"&F1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[kevinb11]

hi try this,

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Employee</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;">
</td><td style=";">From</td><td style="text-align: right;background-color: #FFFF00;;">10:00 AM</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Employee1</td><td style="text-align: right;background-color: #FFFF00;;">10:00 AM</td><td style="text-align: right;background-color: #FFFF00;;">4:00 PM</td><td style="text-align: right;;">
</td><td style=";">To</td><td style="text-align: right;background-color: #9BBB59;;">11:00 AM</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Employee2</td><td style="text-align: right;;">11:00 AM</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">
</td><td style=";">Sumproduct</td><td style="text-align: right;background-color: #C0504D;;">3</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Employee3</td><td style="text-align: right;background-color: #9BBB59;;">8:00 AM</td><td style="text-align: right;background-color: #9BBB59;;">10:00 AM</td><td style="text-align: right;;">
</td><td style=";">Countifs</td><td style="text-align: right;background-color: #F79646;;">3</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Employee4</td><td style="text-align: right;background-color: #FFFF00;;">9:00 AM</td><td style="text-align: right;background-color: #FFFF00;;">6:00 PM</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Employee5</td><td style="text-align: right;background-color: #FFFF00;;">10:30 AM</td><td style="text-align: right;background-color: #FFFF00;;">7:00 PM</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Employee6</td><td style="text-align: right;;">11:30 AM</td><td style="text-align: right;;">8:00 PM</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Sheet2​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F3</th><td style="text-align:left">=SUMPRODUCT(--(B2:B7<f2< font="">),--(C2:C7>F1)</f2<>)</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F4</th><td style="text-align:left">=COUNTIFS(B2:B7,"<"&F2,C2:C7,">"&F1)</td></tr></tbody></table></td></tr></tbody></table>

I think this one would work great. Nice Job. Then you could just work this out for every hour interval throughout the day.

 

[FancyPegLeg]

Wow, there are a few different ways I can go with this. Thanks for the help!

One other problem: the cells of the schedule contain a letter and a space before each start time start, e.g. "S 10:00 PM" = is a server starting at 10pm.

Is there a way to convert a string like that to just the number to make the subsequent formulas work?

Thanks again

 

[GTO]

Do you want to coerce the strings into times in place, or offset to another cell(s) ?(like a formula)