Match/Index to count multiple shifts in a short time span

[SteveNL86]

Dear reader,

I'm a little lost on how to go about solving what I have in mind. First some background info; So I'm working on a huge schedule. To make things a little easier in getting the schedule right I'm trying to spot certain issue's in the schedule. For one, I'd like to know when employees have two shifts on the same day, as this is not allowed. I also am trying to check when they have a shift 1 day right after another shift, and I'm trying to check if they have a shift 2 days after a shift. Finally I'm trying to make sure that if an employee has a weekend shift that they dont have a weekend shift the weekend before and/or after that weekend shift.

So I've figured out how to check for multiple shifts on the same day I think. I have made a helper column, which combines their names and the dates and I check if there are multiples values of those in the column using this formula:

=IF(COUNTIFS($D$2:$D$2500;D2)>1;"FAIL";"PASS")

So my spreadsheet basicly looks like this:

Date	Day	Employee	Combined	Double Shifts	Consequtive Days	2 Days Rest	Weekend Check
01-01-2021	Fri	Bart	01-01-2021_Kelly	PASS	PASS	FAIL	PASS
02-01-2021	Sat	Mark	02-01-2021_Mark	PASS	PASS	PASS	FAIL
03-01-2021	Sun	Kelly	03-01-2021_Kelly	PASS	PASS	FAIL	PASS
04-01-2021	Mon	Rose	04-01-2021_Rose	PASS	PASS	PASS	PASS
05-01-2021	Tue	Bart	05-01-2021_Bart	PASS	FAIL	FAIL	PASS
06-01-2021	Wed	Bart	06-01-2021_Bart	PASS	FAIL	FAIL	PASS
07-01-2021	Thu	Rose	07-01-2021_Rose	PASS	PASS	PASS	PASS
08-01-2021	Fri	Kelly	08-01-2021_Kelly	PASS	PASS	PASS	PASS
09-01-2021	Sat	Mark	09-01-2021_Mark	PASS	FAIL	FAIL	FAIL
10-01-2021	Sun	Mark	10-01-2021_Mark	FAIL	FAIL	FAIL	FAIL
10-01-2021	Sun	Mark	10-01-2021_Mark	FAIL	FAIL	FAIL	FAIL

So I think the double shift check is working but how do I go about checking the other data?

Any help is much appreciated!

Kind regards

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1	Date	Day	Employee	Combined	Double Shifts	Consequtive Days	2 Days Rest	Weekend Check
2	01/01/2021	Fri	Bart	01-01-2021_Kelly
3	02/01/2021	Sat	Mark	02-01-2021_Mark				Fail
4	03/01/2021	Sun	Kelly	03-01-2021_Kelly
5	04/01/2021	Mon	Rose	04-01-2021_Rose
6	05/01/2021	Tue	Bart	05-01-2021_Bart		Fail	Fail
7	06/01/2021	Wed	Bart	06-01-2021_Bart		Fail	Fail
8	07/01/2021	Thu	Rose	07-01-2021_Rose
9	08/01/2021	Fri	Kelly	08-01-2021_Kelly
10	09/01/2021	Sat	Mark	09-01-2021_Mark			Fail	Fail
11	11/01/2021	Mon	Mark	10-01-2021_Mark	Fail	Fail	Fail
12	11/01/2021	Mon	Mark	10-01-2021_Mark	Fail	Fail	Fail
Master

Cell Formulas
Range	Formula
E2:E12	E2	=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"Fail","")
F2:F12	F2	=IF(COUNTIFS(A:A,">="&A2-1,A:A,"<="&A2+1,C:C,C2)>1,"Fail","")
G2:G12	G2	=IF(COUNTIFS(A:A,">="&A2-2,A:A,"<="&A2+2,C:C,C2)>1,"Fail","")
H2:H12	H2	=IF(LEFT(B2)<>"s","",IF(OR(LEFT(IFERROR(INDEX(B3:B12,MATCH(C2,C3:C12,0)),""))="s",LEFT(IFERROR(INDEX(B$1:B1,MATCH(C2,C$1:C1,0)),""))="s"),"Fail",""))
B2:B12	B2	=TEXT(A2,"ddd")

 

[Eric W]

Another option:

Book1
	A	B	C	D	E	F	G	H
1	Date	Day	Employee	Combined	Double Shifts	Consecutive Days	2 Days Rest	Weekend Check
2	1/1/2021	Fri	Bart	01-01-2021_Kelly
3	1/2/2021	Sat	Mark	02-01-2021_Mark				FAIL
4	1/3/2021	Sun	Kelly	03-01-2021_Kelly
5	1/4/2021	Mon	Rose	04-01-2021_Rose
6	1/5/2021	Tue	Bart	05-01-2021_Bart		FAIL	FAIL
7	1/6/2021	Wed	Bart	06-01-2021_Bart		FAIL	FAIL
8	1/7/2021	Thu	Rose	07-01-2021_Rose
9	1/8/2021	Fri	Kelly	08-01-2021_Kelly
10	1/9/2021	Sat	Mark	09-01-2021_Mark		FAIL	FAIL	FAIL
11	1/10/2021	Sun	Mark	10-01-2021_Mark	FAIL	FAIL	FAIL	FAIL
12	1/10/2021	Sun	Mark	10-01-2021_Mark	FAIL	FAIL	FAIL	FAIL
Sheet8

Cell Formulas
Range	Formula
E2:E12	E2	=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"FAIL","")
F2:F12	F2	=IF(SUMPRODUCT(--(ABS($A$2:$A$20-A2)=1),--($C$2:$C$20=C2))>0,"FAIL","")
G2:G12	G2	=IF(SUMPRODUCT(--(ABS($A$2:$A$20-A2)<=2),--($C$2:$C$20=C2))>1,"FAIL","")
H2:H12	H2	=IF(SUM(COUNTIFS(C:C,C2,A:A,A2+IF(B2="Sat",{-7,-6,7,8},IF(B2="Sun",{-7,-8,6,7},{9999}))))>0,"FAIL","")
Press CTRL+SHIFT+ENTER to enter array formulas.

The E/F/G formulas should give the same results as Fluff's, but the Weekend formula works a bit different, it looks like we interpreted the requirement differently.

 

[Fluff]

but the Weekend formula works a bit different, it looks like we interpreted the requirement differently.

Nope, It's just that my formula for col H is wrong.

 

[SteveNL86]

Thank you both massively! I've managed to get the formula's working in my speadsheet. Your solution in column H is working as a charm @Eric W.

Cant thank you enough, this is a massive help!

 

[Eric W]

Glad we could help! Thanks for the update.