Calculating staff on the clock for every 30 minutes

[saconroy66]

In my schedule I show the start and end times for my employees (see below). On a separate sheet I need to know how many people are working for every 30 minutes. For example, from 9am to 9:30am I can manually count that there is only one person working. From 9:30am to10am there will be two people working. And so on for the remainder of the day. With a large crew it takes too long to manually count this. Is there a formula that will calculate this for me?


[TABLE="width: 206"]
<tbody>[TR]
[TD]Ashton
[/TD]
[TD]10:00am
[/TD]
[TD]2:30pm
[/TD]
[/TR]
[TR]
[TD]Brenna
[/TD]
[TD]5:30pm
[/TD]
[TD]9:30pm
[/TD]
[/TR]
[TR]
[TD]Caitlin
[/TD]
[TD]5:00pm
[/TD]
[TD]9:30pm
[/TD]
[/TR]
[TR]
[TD]Cassandra
[/TD]
[TD]9:00am
[/TD]
[TD]5:00pm
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Computerman]

saconroy66,
How are you going to define the time range that you need information on? the calculation would be to take the start time of each employee, one by one, and compare it to the beginning time of your target range. If it matches or is after the beginning of your target range, then compare the employees end time to end time of you target range if it is earlier than or equal to your target end time then add 1 to you cell where you want the count to appear. So you will need a two part if then Statement, with the first part comparing the start times and the second part comparing the end times, put in a loop that checks all the employees ( a Lastrow function can help here) and you should get what you are looking for.

Hope that helps,
Computerman

 

[saconroy66]

Okay I think I understand the concept, but I have no idea what that formula looks like. Can you help me?

Thanks!

 

[saconroy66]

Hi again, I am hoping there is someone out there that can help me with this formula. I understand what needs to be done, but I do not know what the formula is. Can anyone help me?

 

[Metallik]

 

[Metallik]

Greetings,

I uploaded a pic...I am trying to build it...to be able to enter the start and stop time of the schedule, enter the days off, and the number of each schedule so that it will show the staff by 30 minute intervals. Right now, i have it counting the number of staff based on the start and stop time only using SumProduct....can someone see if they can figure out how to incorporate the "Team Size" and the Days Off?