Help with formula for scheduling staff

[ardykav]

Hi All,

I need some help with a formula. In Column A I have the persons name, Column B i have the start time of a persons shift. In column C i have the end time of that shift. The shifts can be any back to back combination of 5 minutes from 7am to 8pm so there are a total of 156 possible shifts in a given day. I have created a list which names each 5 minute interval also from 1 to 156

What i want to happen is that if a shift is entered but it contradicts a shift already there then it shows up as an error. Can have this as a conditional format of a cell or an error message in Column D whichever is the easiest.

So for example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Shift Start[/TD]
[TD]Shift End[/TD]
[TD]Error Check[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]John Jones[/TD]
[TD]7am[/TD]
[TD]10am[/TD]
[TD]Shift coindcides with another shift[/TD]
[TD]14/04/16[/TD]
[/TR]
[TR]
[TD]John Jones[/TD]
[TD]10am[/TD]
[TD]11am[/TD]
[TD][/TD]
[TD]14/04/16[/TD]
[/TR]
[TR]
[TD]John Jones[/TD]
[TD]8am[/TD]
[TD]9am[/TD]
[TD]Shift coindcides with another shift[/TD]
[TD]14/04/16[/TD]
[/TR]
</tbody>[/TABLE]


thanks in advance

 

[NotoriousPopol]

Could you be more specific, for example can you have multiple people on the same shift? A shift coincides with an other one if either the shift start or shift end is in the range of an other sheet?

 

[ardykav]

Could you be more specific, for example can you have multiple people on the same shift? A shift coincides with an other one if either the shift start or shift end is in the range of an other sheet?

Apologies I should have been clearer. So it is only when one person is scheduled to do two shifts at overlapping times. For each shift an employee is assigned to look after a certain person so I dont want to double book them for seeing 2 people at the one time.

I tried a concatenate but this didnt work when the start or end times werent exactly the same for the differing shifts.

I just dont want to double book someone if that makes sense.

So if I put someone down to work 9-12 I want a flag to be raised say if i put them down then to work at 10-11 also.

 

[ardykav]

Anyone got any ideas for this one?

Apologies I should have been clearer. So it is only when one person is scheduled to do two shifts at overlapping times. For each shift an employee is assigned to look after a certain person so I dont want to double book them for seeing 2 people at the one time.

I tried a concatenate but this didnt work when the start or end times werent exactly the same for the differing shifts.

I just dont want to double book someone if that makes sense.

So if I put someone down to work 9-12 I want a flag to be raised say if i put them down then to work at 10-11 also.

 

[NotoriousPopol]

I have a suggestion, but it's not ideal. I did it with simple numbers so you may have to transform the formula a bit : =IF(AND(MIN(INDEX($A$1:$C$11,MATCH(A1,$A$1:$A$11,0),2))>C1,MAX(INDEX($A$1:$C$11,MATCH(A1,$A$1:$A$11,0),3))<b1)," !!!="" ","").

So, I compare the finishing time (C1) with the min of the starting time for the same guy, and the starting date (B1) with the max of the finishing time for the same guy. If one of this condition is not correct, I display " !!! ".

EDIT : THIS DO NOT WORK. I keep looking...</b1),">

 

[ardykav]

I have a suggestion, but it's not ideal. I did it with simple numbers so you may have to transform the formula a bit : =IF(AND(MIN(INDEX($A$1:$C$11,MATCH(A1,$A$1:$A$11,0),2))>C1,MAX(INDEX($A$1:$C$11,MATCH(A1,$A$1:$A$11,0),3))<b1)," !!!="" ","").

So, I compare the finishing time (C1) with the min of the starting time for the same guy, and the starting date (B1) with the max of the finishing time for the same guy. If one of this condition is not correct, I display " !!! ".

EDIT : THIS DO NOT WORK. I keep looking...</b1),">

thanks greatly appreciated

 

[NotoriousPopol]

Ok, after a few hours looking how to solve this with VLOOKUP or INDEX or SMALL or MATCH... I finally found something that seems to work. This is what I suggest : =SUM(($B$1:$B9<$B10)*($C$1:$C9>$B10)*($A$1:$A9=$A10),($B$1:$B9<$C10)*($C$1:$C9>$C10)*($A$1:$A9=$A10),($B$1:$B9>$B10)*($C$1:$C9<$C10)*($A$1:$A9=$A10)).

In my formula, I have the name in column A, start col B, end col C. This formula goes into D10, last row of my test. This will return the sum of occurrence of at least one of the following :
- a shift above this one has a lower starting time AND a greater finish time than this starting time
- a shift above this one has a lower starting time AND a greater finish time than this finish time
- a shift above has a greater starting time than this starting time AND a lower finish time than this finish time

As you can see in the formula, there is 3 parts in the sum, corresponding to this 3 conditions. In each part, I check the condition AND the name : ($A$1:$A$9=$A10). You can for example put this formula in your last row, change the references, and extend to the top. Then you can apply a conditionnal formatting on this column (0 is OK, >0 not OK).