kateJarvis
New Member
- Joined
- Feb 8, 2020
- Messages
- 3
- Office Version
- 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:
An example is:
I cannot seem to attach an excel doc example.. sorry
Any help would be much appreciated
Thank you
Kate
The issues are:
- I don't know how to do this!
- The roster is made up of text in the cells
- there is a gap between the x2 2 week blocks
- the zero is a letter o representing day off.
- on call also is considered a day off ie not counted as a consecutive shift
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