Consecutive Worked Shifts in a Period

[PTruscott]

Hi All

Back Again with another request

I am chasing a formula that looks back from TODAY() and counts consecutive working shifts within the previous 4 weeks (28 days)

Working shifts are designated as D and N

Where any value/text other than D or N is encountered then the 'count' stops

I have attached a dummy roster sheet with example

Roster Consecutive Shifts Example.xlsx

Any help appreciated

Ta

Pete

[FlameRetired]

I haven't been able to come up with a shorter/simpler way. Sorry

Try array entering this beast. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

It returns 14 for TODAY() (5/30/2019). I believe that's correct. Doing it manually reveals the same.

[FlameRetired]

Forgot to mention today's date is in D6.

[PTruscott]

Hi Dave

Thanks for that beast of a formula. I placed it in and it calculates as suggested. However on reflection I may not have explained the request properly.

I only want to count consecutive shifts from TODAY() back. Not consecutive shifts within the 28 day period. Does that make sense?

So if TODAY() is a D or N day it counts back consecutive D or N from today's date until it hits a non D or N value within the 4 week period. If TODAY() is a non D or N day then it shows 0

The policy I am trying to monitor against is per below for fatigue Management

• The number of consecutive days worked in a 4 week cycle will not exceed 21 days;

Thanks heaps

Pete

[FlameRetired]

If I understand correctly now please try changing SUM to MAX and array enter that. With today's date 6/2/2019 this returns 14.

Or am I still missing it?

Please Login or Register  to view this content.

[FlameRetired]

This is a lot shorter. Still array entered.

Please Login or Register  to view this content.

[PTruscott]

Hi Flame

Thanks heaps fro those emails and formulas.

I think we are still on slightly different paths

I have attached an updated spread sheet with your 3 x formulas and 2 x examples with manual calculated answers

Attachment 626909

What I am chasing is formula to give answers

I hope that makes sense

Thanks heaps

Pete

[JeteMc]

Attempting to open attachment 626909 (post #7) results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
Let us know if you have any questions.