Counting consecuative shifts

kateJarvis

New Member
Joined
Feb 8, 2020
Messages
3
Office Version
  1. 2010
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 shiftsRoster B number of shiftsMax number of shifts in a row
MoTuWeThFrSaSuMoTuWeThFrSaSu MoTuWeThFrSaSuMoTuWeThFrSaSu
23456789101112131415 FTERDO1617181920212223242526272829
MOBOMOB1PMMOBOOMOBAC-EAC-EO4PM1PM1PMN14 TH-TRTH-TRADOOOOOTH-TRTH-TRTH-TRTH-TRTH-TRTH-TRTH-TRN
10​
10​
7​
FL-TRFL-TRFL-TRFL-TROOOADOFL-TRFL-TRFL-TRFL-TR1PMO 14 OFLFLFL1PMOon call4PM4PM OAC-EMOBMOB
10​
10​
6​
GENADOGEN8MOBGENO1PMOMOBGENOOMOBMOB 14 4PM4PM4PMOMOB1PMOMOBMOBMOBAC-L4PMOO
10​
10​
5​
ACC-TRACC-TRAC-LAC-LAC-MOO1PM-TR1PM-TR1PM3PM1PMOOG14 MOBMOBADOAC-EOMOBMOB3PM3PM3PM3PMOOOG
10​
10​
7​

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

Any help would be much appreciated
Thank you
Kate
 
Of course I do it in pieces. I regard it as a "good practice".
I create parts, test them separately and then assemble, and then test again the whole - because it is possible to make mistakes while assembling.

J.Ty.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi kateJarvis,

The 2 blocks should be considered as one, that is, in the following example, the greatest number of consecutive shifts is 9 or 7?

feb9.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2MoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSu
323456789101112131415FTERDO1617181920212223242526272829
4MOBOMOB1PMMOBOOOAC-EAC-EAC-E4PM1PM1PMN14TH-TRTH-TRADOOOOOTH-TRTH-TRTH-TRTH-TRTH-TRTH-TRTH-TR
Sheet

_______________________________________________________________________
If they are independent, here another array formula for you to consider:
varios 09feb2020b.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1
2MoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuResult
323456789101112131415FTERDO1617181920212223242526272829
4MOBOMOB1PMMOBOOOAC-EAC-EAC-E4PM1PM1PMN14TH-TRTH-TRADOOOOOTH-TRTH-TRTH-TRTH-TRTH-TRTH-TRTH-TRN7
5FL-TRFL-TRFL-TRFL-TROOOADOFL-TRFL-TRFL-TRFL-TR1PMO14OFLFLFL1PMOon call4PM4PMOAC-EMOBMOB6
6GENADOGEN8MOBGENO1PMOMOBGENOOMOBMOB144PM4PM4PMOMOB1PMOMOBMOBMOBAC-L4PMOO5
7ACC-TRACC-TRAC-LAC-LAC-MOO1PM-TR1PM-TR1PM3PM1PMOOG14MOBMOBADOAC-EOMOBMOB3PM3PM3PM3PMOOOG6
Hoja3
Cell Formulas
RangeFormula
AH4:AH7AH4=MAX(MAX(FREQUENCY(IF((A4:N4<>"O")+(A4:N4<>"on call")=2,COLUMN(A4:N4)),IF((A4:N4<>"O")+(A4:N4<>"on call")<2,COLUMN(A4:N4)))),MAX(FREQUENCY(IF((S4:AF4<>"O")+(S4:AF4<>"on call")=2,COLUMN(S4:AF4)),IF((S4:AF4<>"O")+(S4:AF4<>"on call")<2,COLUMN(S4:AF4)))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top