Formula to count max employees at any time

[asad]

Hi Guys,

I have the following times of duty for our employees. What I would like to do is count the maximum number of employees that will be on site during the day. Can someone please help me? There are different employee number for different days. Data below shows 4 of the days. The actual table is much bigger with a lot more employees.

[TABLE="width: 597"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]Meal Start[/TD]
[TD]Meal End[/TD]
[TD]Finish[/TD]
[TD]Total[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]03:33[/TD]
[TD]06:27[/TD]
[TD]07:27[/TD]
[TD]11:44[/TD]
[TD]07:11[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]03:45[/TD]
[TD]07:30[/TD]
[TD]08:16[/TD]
[TD]12:33[/TD]
[TD]08:02[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]03:48[/TD]
[TD]07:48[/TD]
[TD]08:37[/TD]
[TD]12:47[/TD]
[TD]08:10[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]04:03[/TD]
[TD]06:56[/TD]
[TD]07:41[/TD]
[TD]11:58[/TD]
[TD]07:10[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]04:18[/TD]
[TD]08:30[/TD]
[TD]09:19[/TD]
[TD]12:12[/TD]
[TD]07:05[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]04:33[/TD]
[TD]08:43[/TD]
[TD]09:33[/TD]
[TD]13:43[/TD]
[TD]08:20[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]05:03[/TD]
[TD]09:21[/TD]
[TD]10:01[/TD]
[TD]12:59[/TD]
[TD]07:16[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]05:10[/TD]
[TD]09:28[/TD]
[TD]10:15[/TD]
[TD]13:08[/TD]
[TD]07:11[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]05:25[/TD]
[TD]09:47[/TD]
[TD]10:43[/TD]
[TD]13:36[/TD]
[TD]07:15[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]05:40[/TD]
[TD]10:25[/TD]
[TD]11:25[/TD]
[TD]14:18[/TD]
[TD]07:38[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]05:55[/TD]
[TD]10:10[/TD]
[TD]11:04[/TD]
[TD]14:02[/TD]
[TD]07:13[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]06:03[/TD]
[TD]10:53[/TD]
[TD]11:53[/TD]
[TD]16:07[/TD]
[TD]09:04[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]06:33[/TD]
[TD]11:14[/TD]
[TD]12:14[/TD]
[TD]16:33[/TD]
[TD]09:00[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]08:18[/TD]
[TD]11:28[/TD]
[TD]12:28[/TD]
[TD]16:42[/TD]
[TD]07:24[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]08:20[/TD]
[TD]12:38[/TD]
[TD]13:38[/TD]
[TD]18:01[/TD]
[TD]08:41[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]08:50[/TD]
[TD]13:13[/TD]
[TD]14:13[/TD]
[TD]18:43[/TD]
[TD]08:53[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]09:07[/TD]
[TD]12:17[/TD]
[TD]13:17[/TD]
[TD]17:40[/TD]
[TD]07:33[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]11:34[/TD]
[TD]14:27[/TD]
[TD]15:23[/TD]
[TD]19:49[/TD]
[TD]07:19[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]11:41[/TD]
[TD]14:44[/TD]
[TD]15:44[/TD]
[TD]19:57[/TD]
[TD]07:16[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]12:30[/TD]
[TD]15:18[/TD]
[TD]16:05[/TD]
[TD]20:21[/TD]
[TD]07:04[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]12:44[/TD]
[TD]15:37[/TD]
[TD]16:19[/TD]
[TD]20:37[/TD]
[TD]07:11[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]13:19[/TD]
[TD]16:16[/TD]
[TD]17:15[/TD]
[TD]21:21[/TD]
[TD]07:03[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]13:54[/TD]
[TD]16:50[/TD]
[TD]17:36[/TD]
[TD]21:51[/TD]
[TD]07:11[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]14:15[/TD]
[TD]18:35[/TD]
[TD]19:17[/TD]
[TD]22:11[/TD]
[TD]07:14[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]15:04[/TD]
[TD]19:23[/TD]
[TD]20:23[/TD]
[TD]24:21[/TD]
[TD]08:17[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]15:53[/TD]
[TD]20:03[/TD]
[TD]20:53[/TD]
[TD]24:51[/TD]
[TD]08:08[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]16:05[/TD]
[TD]20:23[/TD]
[TD]21:23[/TD]
[TD]25:21[/TD]
[TD]08:16[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]16:20[/TD]
[TD]20:43[/TD]
[TD]21:43[/TD]
[TD]25:45[/TD]
[TD]08:25[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]16:21[/TD]
[TD]20:53[/TD]
[TD]21:53[/TD]
[TD]25:52[/TD]
[TD]08:31[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]16:35[/TD]
[TD]21:13[/TD]
[TD]22:13[/TD]
[TD]24:56[/TD]
[TD]07:21[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]19:28[/TD]
[TD]22:33[/TD]
[TD]23:33[/TD]
[TD]28:00[/TD]
[TD]07:32[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]19:48[/TD]
[TD]22:55[/TD]
[TD]23:55[/TD]
[TD]28:30[/TD]
[TD]07:42[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]19:58[/TD]
[TD]23:46[/TD]
[TD]24:31[/TD]
[TD]29:05[/TD]
[TD]08:22[/TD]
[TD]Mon,Tue,Wed[/TD]
[/TR]
[TR]
[TD]03:33[/TD]
[TD]07:38[/TD]
[TD]08:25[/TD]
[TD]11:25[/TD]
[TD]07:05[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]03:45[/TD]
[TD]07:31[/TD]
[TD]08:31[/TD]
[TD]12:49[/TD]
[TD]08:04[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]03:48[/TD]
[TD]07:52[/TD]
[TD]08:37[/TD]
[TD]12:40[/TD]
[TD]08:07[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]04:03[/TD]
[TD]08:13[/TD]
[TD]09:13[/TD]
[TD]13:25[/TD]
[TD]08:22[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]04:18[/TD]
[TD]07:03[/TD]
[TD]07:43[/TD]
[TD]11:55[/TD]
[TD]06:57[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]04:33[/TD]
[TD]07:24[/TD]
[TD]08:07[/TD]
[TD]12:30[/TD]
[TD]07:14[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]05:19[/TD]
[TD]09:34[/TD]
[TD]10:25[/TD]
[TD]14:43[/TD]
[TD]08:33[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]05:34[/TD]
[TD]10:07[/TD]
[TD]11:07[/TD]
[TD]15:21[/TD]
[TD]08:47[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]05:41[/TD]
[TD]08:37[/TD]
[TD]09:25[/TD]
[TD]13:42[/TD]
[TD]07:13[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]05:55[/TD]
[TD]10:25[/TD]
[TD]11:25[/TD]
[TD]14:24[/TD]
[TD]07:29[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]06:09[/TD]
[TD]10:37[/TD]
[TD]11:37[/TD]
[TD]16:02[/TD]
[TD]08:53[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]06:23[/TD]
[TD]10:55[/TD]
[TD]11:55[/TD]
[TD]16:20[/TD]
[TD]08:57[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]06:44[/TD]
[TD]11:31[/TD]
[TD]12:31[/TD]
[TD]16:56[/TD]
[TD]09:12[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]07:05[/TD]
[TD]10:10[/TD]
[TD]10:55[/TD]
[TD]13:49[/TD]
[TD]05:59[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]07:19[/TD]
[TD]12:01[/TD]
[TD]13:01[/TD]
[TD]17:32[/TD]
[TD]09:13[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]07:45[/TD]
[TD]12:31[/TD]
[TD]13:31[/TD]
[TD]17:55[/TD]
[TD]09:10[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]08:50[/TD]
[TD]13:25[/TD]
[TD]14:25[/TD]
[TD]18:59[/TD]
[TD]09:09[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]09:38[/TD]
[TD]13:45[/TD]
[TD]14:43[/TD]
[TD]19:08[/TD]
[TD]08:32[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]09:56[/TD]
[TD]14:03[/TD]
[TD]15:01[/TD]
[TD]19:35[/TD]
[TD]08:41[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]10:08[/TD]
[TD]14:31[/TD]
[TD]15:31[/TD]
[TD]19:50[/TD]
[TD]08:42[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]12:02[/TD]
[TD]14:53[/TD]
[TD]15:53[/TD]
[TD]20:25[/TD]
[TD]07:23[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]13:14[/TD]
[TD]16:11[/TD]
[TD]17:07[/TD]
[TD]21:21[/TD]
[TD]07:11[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]13:32[/TD]
[TD]17:52[/TD]
[TD]18:46[/TD]
[TD]24:01[/TD]
[TD]09:35[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]13:50[/TD]
[TD]16:47[/TD]
[TD]17:35[/TD]
[TD]21:51[/TD]
[TD]07:13[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]13:56[/TD]
[TD]16:53[/TD]
[TD]17:42[/TD]
[TD]20:43[/TD]
[TD]05:58[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]14:02[/TD]
[TD]18:20[/TD]
[TD]19:10[/TD]
[TD]24:21[/TD]
[TD]09:29[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]15:32[/TD]
[TD]19:48[/TD]
[TD]20:33[/TD]
[TD]25:45[/TD]
[TD]09:28[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]15:52[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]20:19[/TD]
[TD]04:27[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]16:20[/TD]
[TD]20:30[/TD]
[TD]21:23[/TD]
[TD]25:21[/TD]
[TD]08:08[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]16:27[/TD]
[TD]20:53[/TD]
[TD]21:53[/TD]
[TD]25:52[/TD]
[TD]08:25[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]16:34[/TD]
[TD]21:13[/TD]
[TD]22:13[/TD]
[TD]26:05[/TD]
[TD]08:31[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]19:28[/TD]
[TD]22:33[/TD]
[TD]23:33[/TD]
[TD]28:00[/TD]
[TD]07:32[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]19:48[/TD]
[TD]22:55[/TD]
[TD]23:55[/TD]
[TD]28:30[/TD]
[TD]07:42[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]20:48[/TD]
[TD]24:43[/TD]
[TD]25:43[/TD]
[TD]29:00[/TD]
[TD]07:12[/TD]
[TD]Thu

[/TD]
[/TR]
</tbody>[/TABLE]


Thanks and Regards
Asad

 

[DanteAmor]

I do not know if a formula can get the result. But if you want with a macro I can give you the result of maximum employees per day.
Just tell me in which cell your data begins.

 

[asad]

Thanks a lot DanteAmor.
Macro is fine with me. The data begins in cell B3.
Thanks
Asad

 

[DanteAmor]

I would like to do is count the maximum number of employees that will be on site during the day

Hi Asad, reviewing your data, I really do not know what the count is.
You could explain with the example you put, what you want to count and what the result is.
You want to know the maximum number of employees are at a time of the day. Or in time ranges from 3 to 4, from 4 to 5, from 5 to 6, from 6 to 7, etc. Or in a range of schedules.
There are several possibilities and I prefer an explanation.

 

[Repush]

you could use SUMPRODUCT . .
the # of people on site at time O1 is:

=SUMPRODUCT(--($A$2:$A$68 < O$1),--($D$2:$D$68 > O$1))

<o$1),--($d$2:$d$68>
(time O1 is between value in col A and value in col D)

the # of people havinfg a meal at time O1 is:

=SUMPRODUCT(--($B$2:$B$68 < O$1);--($C$2:$C$68 > O$1))<o$1),--($c$2:$c$68>

(time O1 is between value in col B and value in col C)

the # of people on site NOT having a meal at time O1 is:

=SUMPRODUCT(--($A$2:$A$68 < O$1),--($D$2:$D$68 > O$1)) - SUMPRODUCT(--($B$2:$B$68 < O$1),--($C$2:$C$68 > O$1))

</o$1),--($c$2:$c$68></o$1),--($d$2:$d$68>

 

[Repush]

oh . . and make colums with different time-stamps (in O1, P1, Q1 etc.), copy the formula to each col . .
and you will find at 10:00 there are 32 people, the max of the day

 

[DanteAmor]

oh . . and make colums with different time-stamps (in O1, P1, Q1 etc.), copy the formula to each col . .
and you will find at 10:00 there are 32 people, the max of the day

I would like to do is count the maximum number of employees that will be on site during the day

What I did not understand about the question is if you need the number of concurrent employees. That makes more sense.
Because people who work in a day are 34, schedules that do not have concurrency are only 2: 03:33 and 24:31:00
But there are not 32 employees in the day, in any case 34 people are the people who worked on the day.
But concurring, for example at 9:19 is 16, is the maximum number of concurrency in a schedule.

 

[Repush]

you are probably right . . I ignored the day-column
let's wait for asad's reply (probably in deep sleep by now)

 

[DanteAmor]

let's wait

 

[asad]

Many thanks Rupesh and DanteAmor.
I want maximum concurrent employees which should be 21 at about 11:41 in the above example. This is taking into account all the employees coming in the car park and any employee leaving the car park. So I think I should add 10 minutes to my calculations on both sides or rather subtract 10 minutes from start time and add 10 minutes to the finish time.
What do you guys suggest?