Track employees which cross shifts and counted one time each shift

I hung in for days trying to find the info here, but I think it's beyond my ability.

I'm trying to track the number of employees on each shift while writing the schedule. I copied one column of the schedule and what I thought would work.

Can any of you point me in the right direction?

Note, some employees cross shifts and need to be counted one time on each shift.

Originally Posted by TMShucks

Welcome to the forum. A couple of suggestions before we come to the formalities ... 1) maybe add an explanation to your sample workbook so we better understand your logic; which entries are counted in which slot and why? Are you counting the In or the Out? 2) it would probably help to split the time ranges into separate start and end cells.

Thanks for the welcome...

My logic is best described in the sample math in the red box's. Simply put (but not as simply accomplished for me), I just needed the red box's to add 1 each time a person is scheduled for that shift segment.

The only one I could get to work as expected is 9pm 12:30am =SUM(COUNTIF($D$7:$D$48,{">=11:59:00 PM","<=12:30 AM"})*{1,1})

I'm going to assume what I need is better accomplished by separating the "IN" times to one column and the "OUT" times to another. Which I have already done. The post here was more of a "I give up, was this ever even possible"? Since I wasted 3 days trying to do it when I should have separated the columns day 1.

Live and learn I guess ehhh?

Originally Posted by TMShucks

And, the formalities ... not entirely sure what happened there but the thread title has magically changed so forget what I was going to say about formalities

I think it was probably a helpful moderator. Oddly enough I read the rules and still managed to let my frustration empty my head out.

OK here is what I came up with and I still have an error...

The period in red is not calculating correctly and adding 1 to each shift segment as expected. I have hit a wall and I can't get past this. It seems any shift that goes past 12 AM is ignored. Can one of you experts lend a hand?

Sample work book attached...

Maybe if I show a clearer example, or is what I am attempting just simply not possible?

new workbook sample...

You need to incorporate a check to see if start-time is greater than finish time, like this in M9 of your 2t29 workbook:

=SUMPRODUCT((MOD(I$3:I$7,1)<K9+(I$3:I$7>J$3:J$7))*(MOD(J$3:J$7,1)+(I$3:I$7>J$3:J$7)>J9))

then copy this down.

Hope this helps.

Pete

Unfortunately, neither of these work, but I do appreciate the help.

My formula also works, giving the results 3, 3, 3 and 1 which is what you had in column F. Are these not what you expected?

See attached file.

Pete

Thanks for the help, I have decided it is not possible and I see no reason to drive you guys nuts with this.

I will be ending shifts @ 11:59 pm for the schedule purposes.


Thanks again for the help.