calculating the number of consecutive shifts in a 6 week schedule

[Neal Miller]

Date Nea

Saturday, January 31, 2004 OF
Sunday, February 01, 2004 OF
Monday, February 02, 2004 A
Tuesday, February 03, 2004 AM 1/2 da
Wednesday, February 04, 2004 A
Thursday, February 05, 2004 A
Friday, February 06, 2004 A

Saturday, February 07, 2004 OF
Sunday, February 08, 2004 OF
Monday, February 09, 2004 A
Tuesday, February 10, 2004 A
Wednesday, February 11, 2004 A
Thursday, February 12, 2004 A
Friday, February 13, 2004 A

CLOSED WEEKEND
Saturday, February 14, 2004 A
Sunday, February 15, 2004 TB
Monday, February 16, 2004 A
Tuesday, February 17, 2004 A
Wednesday, February 18, 2004 A
Thursday, February 19, 2004 A
Friday, February 20, 2004 A

Saturday, February 21, 2004 OF
Sunday, February 22, 2004 OF
Monday, February 23, 2004 A
Tuesday, February 24, 2004 A
Wednesday, February 25, 2004 A
Thursday, February 26, 2004 A
Friday, February 27, 2004 A

Saturday, February 28, 2004 A
Sunday, February 29, 2004 OF
Monday, March 01, 2004 A
Tuesday, March 02, 2004 A
Wednesday, March 03, 2004 A
Thursday, March 04, 2004 A
Friday, March 05, 2004 A

NUMBER OF DAYS OFF
NUMBER OF VACATION DAYS
MOST CONSECUTIVE DAYS

In the chart above I am trying to calculate the highest number of consecutive days worked. I am having trouble finding the function or just not getting how to wrap my head around the problem any help is greatly appreciate

I am using countif statments for the days off and vacation days

Neal Mille

 

[Jonathan Rynd]

In the chart above I am trying to calculate the highest number of
consecutive days worked.

I'm not sure what you mean. Could you be more specific?

What is the meaning of the blank rows in the table? How do you determine
if a day counts towards "consecutiveness"?

 

[neal]

anything that is not "off" is considered a concecutive day. I simply want to detirmine the has number of days in a row an employee works.

 

[Jonathan Rynd]

In the chart above I am trying to calculate the highest number of
consecutive days worked. I am having trouble finding the function or
just not getting how to wrap my head around the problem any help is
greatly appreciated

Two ways to do this. One with a visual basic user defined function (UDF),
one by inserting an additional column. I'll write out the VB since you
probably don't want to make changes to the layout of your spreadsheet.

Go into the visual basic editor, insert a module, and paste this in.

Function ConsecutiveDays(r As Range, breakstr As String)
Dim consec As Integer, maxconsec As Integer
Dim i As Integer
For i = 1 To r.Count
If r(i).Value <> "" Then
If r(i).Value = breakstr Then
If consec > maxconsec Then maxconsec = consec
consec = 0
Else
consec = consec + 1
End If
End If
Next i
if consec > maxconsec then
ConsecutiveDays = consec
else
ConsecutiveDays = maxconsec
end if
End Function

call it using
=ConsecutiveDays(B3:B42,"OFF")