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

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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:

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

So my spreadsheet basicly looks like this:
DateDayEmployeeCombinedDouble ShiftsConsequtive Days2 Days RestWeekend Check
01-01-2021FriBart01-01-2021_KellyPASSPASSFAILPASS
02-01-2021SatMark02-01-2021_MarkPASSPASSPASSFAIL
03-01-2021SunKelly03-01-2021_KellyPASSPASSFAILPASS
04-01-2021MonRose04-01-2021_RosePASSPASSPASSPASS
05-01-2021TueBart05-01-2021_BartPASSFAILFAILPASS
06-01-2021WedBart06-01-2021_BartPASSFAILFAILPASS
07-01-2021ThuRose07-01-2021_RosePASSPASSPASSPASS
08-01-2021FriKelly08-01-2021_KellyPASSPASSPASSPASS
09-01-2021SatMark09-01-2021_MarkPASSFAILFAILFAIL
10-01-2021SunMark10-01-2021_MarkFAILFAILFAILFAIL
10-01-2021SunMark10-01-2021_MarkFAILFAILFAILFAIL

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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,383
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCDEFGH
1DateDayEmployeeCombinedDouble ShiftsConsequtive Days2 Days RestWeekend Check
201/01/2021FriBart01-01-2021_Kelly    
302/01/2021SatMark02-01-2021_Mark   Fail
403/01/2021SunKelly03-01-2021_Kelly    
504/01/2021MonRose04-01-2021_Rose    
605/01/2021TueBart05-01-2021_Bart FailFail 
706/01/2021WedBart06-01-2021_Bart FailFail 
807/01/2021ThuRose07-01-2021_Rose    
908/01/2021FriKelly08-01-2021_Kelly    
1009/01/2021SatMark09-01-2021_Mark  FailFail
1111/01/2021MonMark10-01-2021_MarkFailFailFail 
1211/01/2021MonMark10-01-2021_MarkFailFailFail 
Master
Cell Formulas
RangeFormula
E2:E12E2=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"Fail","")
F2:F12F2=IF(COUNTIFS(A:A,">="&A2-1,A:A,"<="&A2+1,C:C,C2)>1,"Fail","")
G2:G12G2=IF(COUNTIFS(A:A,">="&A2-2,A:A,"<="&A2+2,C:C,C2)>1,"Fail","")
H2:H12H2=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:B12B2=TEXT(A2,"ddd")
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,748
Another option:

Book1
ABCDEFGH
1DateDayEmployeeCombinedDouble ShiftsConsecutive Days2 Days RestWeekend Check
21/1/2021FriBart01-01-2021_Kelly    
31/2/2021SatMark02-01-2021_Mark   FAIL
41/3/2021SunKelly03-01-2021_Kelly    
51/4/2021MonRose04-01-2021_Rose    
61/5/2021TueBart05-01-2021_Bart FAILFAIL 
71/6/2021WedBart06-01-2021_Bart FAILFAIL 
81/7/2021ThuRose07-01-2021_Rose    
91/8/2021FriKelly08-01-2021_Kelly    
101/9/2021SatMark09-01-2021_Mark FAILFAILFAIL
111/10/2021SunMark10-01-2021_MarkFAILFAILFAILFAIL
121/10/2021SunMark10-01-2021_MarkFAILFAILFAILFAIL
Sheet8
Cell Formulas
RangeFormula
E2:E12E2=IF(COUNTIFS(A:A,A2,C:C,C2)>1,"FAIL","")
F2:F12F2=IF(SUMPRODUCT(--(ABS($A$2:$A$20-A2)=1),--($C$2:$C$20=C2))>0,"FAIL","")
G2:G12G2=IF(SUMPRODUCT(--(ABS($A$2:$A$20-A2)<=2),--($C$2:$C$20=C2))>1,"FAIL","")
H2:H12H2=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.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,383
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,266
Messages
5,623,722
Members
415,985
Latest member
salman101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top