Counting consecuative shifts

[kateJarvis]

Hi I don't know if this is possible but I want to count the maximum number of consecutive shifts a person is doing in a roster (so I can ensure they are not doing too many in a row)
The issues are:

1. I don't know how to do this!
2. The roster is made up of text in the cells
3. there is a gap between the x2 2 week blocks
4. the zero is a letter o representing day off.
5. on call also is considered a day off ie not counted as a consecutive shift

all of which a cannot change - (not my roster to change - just trying to make mistakes harder to miss)

An example is:

ROSTER 3 A

ROSTER 3B

Rosta A Number of shifts

Roster B number of shifts

Max number of shifts in a row

Mo

Tu

We

Th

Fr

Sa

Su

Mo

Tu

We

Th

Fr

Sa

Su

Mo

Tu

We

Th

Fr

Sa

Su

Mo

Tu

We

Th

Fr

Sa

Su

2

3

4

5

6

7

8

9

10

11

12

13

14

15

FTE

RDO

16

17

18

19

20

21

22

23

24

25

26

27

28

29

MOB

O

MOB

1PM

MOB

O

O

MOB

AC-E

AC-E

O

4PM

1PM

1PM

N

1

4

TH-TR

TH-TR

ADO

O

O

O

O

TH-TR

TH-TR

TH-TR

TH-TR

TH-TR

TH-TR

TH-TR

N

10​

10​

7​

FL-TR

FL-TR

FL-TR

FL-TR

O

O

O

ADO

FL-TR

FL-TR

FL-TR

FL-TR

1PM

O

1

4

O

FL

FL

FL

1PM

O

on call

4PM

4PM

O

AC-E

MOB

MOB

10​

10​

6​

GEN

ADO

GEN8

MOB

GEN

O

1PM

O

MOB

GEN

O

O

MOB

MOB

1

4

4PM

4PM

4PM

O

MOB

1PM

O

MOB

MOB

MOB

AC-L

4PM

O

O

10​

10​

5​

ACC-TR

ACC-TR

AC-L

AC-L

AC-M

O

O

1PM-TR

1PM-TR

1PM

3PM

1PM

O

O

G

1

4

MOB

MOB

ADO

AC-E

O

MOB

MOB

3PM

3PM

3PM

3PM

O

O

O

G

10​

10​

7​

I cannot seem to attach an excel doc example.. sorry

Any help would be much appreciated
Thank you
Kate

 

[kateJarvis]

the titles on the right should be over the last 3 columns,

 

[Legacy 456155]

I want to count the maximum number of consecutive shifts a person is doing in a roster

I can't help you there without code, but I would suggest maybe using conditional formatting.
I'm interested in how this might be solved using formulas. Will watch and see.

 

[J.Ty.]

Formula to enter in AG4:
=MAX(MAX(ISNUMBER(FIND(REPT("1";ROW($B$1:$B$14));TEXTJOIN("";TRUE;IF(LEFT($A4:$N4;1)="o";0;1))))*ROW($B$1:$B$14));MAX(ISNUMBER(FIND(REPT("1";ROW($B$1:$B$14));TEXTJOIN("";TRUE;IF(LEFT($S4:$AF4;1)="o";0;1))))*ROW($B$1:$B$14)))
Confirm it with Ctrl-Shift-Enter. Link to my solution is here.

J.Ty.

 

[J.Ty.]

Beware my signature: probably you will have to replace ";" by "," everywhere.

 

[J.Ty.]

Oh, I have forgotten to tell you: you need to confirm the formula with Ctrl-Shift-Enter, unless you are using the newest Excel from Office 365.

 

[kateJarvis]

Thank you so much, the formula is fantastic and I would NEVER have worked it out! I have 1 issue tho my end users are excel 2010.... public gov system :/ TEXTJOIN is not available in this version... is there an alternative work around? Sorry

 

[J.Ty.]

I am enjoying Sunday in the nature. I will prepare a solution later today.

J.Ty.

 

[J.Ty.]

I have come up with the following formula:
=MAX(MAX(ISNUMBER(FIND(REPT("0",ROW($B$1:$B$14)),--(LEFT($A4,1)="o")&--(LEFT($B4,1)="o")&--(LEFT($C4,1)="o")&--(LEFT($D4,1)="o")&--(LEFT($E4,1)="o")&--(LEFT($F4,1)="o")&--(LEFT($G4,1)="o")&--(LEFT($H4,1)="o")&--(LEFT($I4,1)="o")&--(LEFT($J4,1)="o")&--(LEFT($K4,1)="o")&--(LEFT($L4,1)="o")&--(LEFT($M4,1)="o")&--(LEFT($N4,1)="o")))*ROW($B$1:$B$14)),MAX(ISNUMBER(FIND(REPT("0",ROW($B$1:$B$14)),=--(LEFT($AS,1)="o")&--(LEFT($T4,1)="o")&--(LEFT($U4,1)="o")&--(LEFT($V4,1)="o")&--(LEFT($W4,1)="o")&--(LEFT($X4,1)="o")&--(LEFT($Y4,1)="o")&--(LEFT($Z4,1)="o")&--(LEFT($AA4,1)="o")&--(LEFT($AB4,1)="o")&--(LEFT($AC4,1)="o")&--(LEFT($AD4,1)="o")&--(LEFT($AE4,1)="o")&--(LEFT($AF4,1)="o")))*ROW($B$1:$B$14)))
As you can see it is quite long. Thereofre I have changed it a little to save on length, replacin "1" with "0" in encoding of the shifts. It is ugly now but probably not much can be done about it. The online variant now includes both variants.

BTW: How does the roster work? If it is repeating every 2 weeks, there might be long sequences of shifts which begin at the end of the cycle and continue after re-starting it.
On the other hand, if you continue with roster 3B after completing roster 3A (even sometimes) it can lead to long sequences of shifts which start in 3A and continue in 3B.

J.Ty.

 

[Legacy 456155]

My head is spinning.
J.Ty. Are you able to write that out as is? Or do you build it in pieces and then bring it together?