Calculate people present in 15 minute intervals

[xhermanson]

Hello, I need some assistance. I am trying to calculate how many people are present in a given time interval (15 minutes). I have listed the person, start time, end time, lunch start time, lunch end time, Breaks (2) start & end times all separate. On a separate sheet I have a set of times 5am - 5pm in 15 minute intervals. I listed the start & end times for each of these as well. I am trying to come up with a formula that will see how many people are present at that given time. My thoughts are countifs but I am seeing this is very difficult. Any assistance would be helpful (want to see how many employees are available at any given time). Thanks!!

 

[bobsan42]

Hello
I hope I can help.
However to simplify the task you need to adjust the data a bit. You will have to ditch the lunch and break times as separate columns.
Normally the data set will need 4 columns: person/start_time/end_time/comments (optional)
Remarks for breaks and lunch can go in the comments column.
If Breaks and lunch are to be counted as absence then :
1. break start must go in as end_time
2. break end must go in as start_time (if the person comes back after break or lunch).
After you set the data in this way the task becomes quite simple - we have to check if any two time periods are overlapping partially or not.

Let's say you have two periods of time: T1 and T2.
By definition if start of T1 is after the end of T2, or if the end of T1 is before the start of T2 then the two period have nothing in common.
So in summary what you have to do is: count all records then subtract all periods that do not intersect.

For any given time interval (no matter if it is 15 minutes or not) if we say that start is TS(e.g.05:00), and the end is TE (e.g. 05:15) to find all persons present in this period you will need a formula like:

[B]=CountA([I]column with all persons[/I]) - Count(start_time column,">=" & TE) - Count(end_time column,"<=" & TS)[/B]

HTH