Count Consecutive Days while bridging over days off and holidays

If you post a workbook with some sample data and the results you expect it will be much easier to help you.

I'll get back to this in one day.

Please explain the reference to 'fmla' in your original and where this relates in the uploaded workbook.

I think fmla is just another type of leave that would show up as red in his chart. https://en.wikipedia.org/wiki/Family...ve_Act_of_1993

This is quite a strange calendar. It starts on Wednesday and it appears that Sunday is the only day when there is no activity. In addition, how are future dates added to this?

I'm mostly thinking about the actual algorithm, the rest can be fixed. In my mind it has to loop around a lot to test for all combinations of consecutive days. And the IF's for the green days and so on. One of those problems that look innocent at first glance and then blow up in your face.

We're better able to advise on the algorithm if we know exactly how your end result is calculated. Hence my question seeking to know which cells contribute to your result. It may well be that you don't need a macro or UDF at all. Often standard functions combined in a complex formula are sufficient.

I have written a simple User Defined Function (UDF) called CONSEQOFF() that you can install into our workbooks as needed and it will provide the new count function you need.

Option Explicit

Function CONSEQOFF(Workdays As Range) As Long
Dim Cnt As Long, cell As Range

For Each cell In Workdays
    Select Case cell.Value
        Case Is = ""
            CONSEQOFF = WorksheetFunction.Max(CONSEQOFF, Cnt)
            Cnt = 0
        Case Is = "MIA", "MIAOUT", "UPTO"
            Cnt = Cnt + 1
        Case Is = "HOLOFF", "DAYOFF"
            'skip, do nothing
    End Select
Next cell
CONSEQOFF = WorksheetFunction.Max(CONSEQOFF, Cnt)

End Function

After it installed, it is used in a cell like any other function.

=CONSEQOFF(Workdays)

Workdays = the range of cells you want to evaluate


=========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

If I understand this problem correctly, cannot HOLOFF, Dayoff and Blank be considered the same thing and give all of those say a value of 0. Then, consider all of the others (MIA, MIAOUT and UPTO) the same and give them a value of 1. It appears to me, that no matter how many days are worked or days were off, the answer will be the sum of the 1s. I can't see anything in the example to give me a different slant on it but it seems to be too simple to be correct.

Row 5 as I understand the rules should be 3 and not 2.

I created a lookup table in A19:B24 assigning the 1s and 0s for the different leaves, holidays and work days.

I created a separate table using VLOOKUP and directly referenced the example table using the following:

Formula:

=IFERROR(VLOOKUP(C2,$A$19:$B$24,2,0),0)

Then it was a matter of summing the 1s in each row:

Formula:

=SUM(M2:R2)

Originally Posted by newdoverman

...cannot HOLOFF, Dayoff and Blank be considered the same thing and give all of those say a value of 0.

No, HOLOFF and DAYOFF would not reset the "counting" of other non-planned days off, would not reset the count to zero.
BLANK resets the count to zero in terms of consecutive days off since the person worked.

Row 5 would be 2 as G:H are the counted cells.

It looks like the work schedule for patients at a nursing home

Though the logic appears valid and it yields the expected results I am not particularly confident of this one.

Array entered in K2 / filled down.

=SUM(IFERROR(((TRANSPOSE(C2:H2)={"MIAOUT","MIA","UPTO"})+(-1*(TRANSPOSE(D2:H2)={"","<>DAYOFF","<>HOLOFF"}))),FALSE))+1

Edit: TRANSPOSE was unnecessary. Loose ends from the build. My bad. This instead.

=SUM(IFERROR(((C2:H2={"MIAOUT";"MIA";"UPTO"})+(-1*(D2:H2={"";"<>DAYOFF";"<>HOLOFF"}))),FALSE))+1

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Name	Max Consecutive Days	1-Jul	2-Jul	3-Jul	4-Jul	6-Jul	7-Jul		Expected Results in column B
2	Bob Jones	6	MIAOUT	DAYOFF	MIAOUT	MIAOUT	MIAOUT	MIAOUT		5	5
3	John Smith	6	MIAOUT	MIAOUT	MIAOUT	DAYOFF	MIAOUT	MIAOUT		5	5
4	Bill Joe	6	HOLOFF	MIA	MIA	MIA	MIA	MIA		5	5
5	George Washington	3	HOLOFF	UPTO		DAYOFF	UPTO	MIA		2	2
6	Thomas Jefferson	6	UPTO	UPTO	UPTO	DAYOFF	UPTO	UPTO		5	5
7	Ben Franklin	6	MIAOUT	DAYOFF	MIAOUT	HOLOFF	MIA	MIA		4	4
8	Sam Adams	6	DAYOFF	UPTO	UPTO	HOLOFF	UPTO	UPTO		4	4
9	John Hamilton	5		DAYOFF	UPTO	HOLOFF	UPTO	UPTO		3	3

The above wasn't working. When column H items were changed to DAYOFF for example the results wouldn't budge. (I thought I'd checked everything out.)

It works with this one.

=SUM(IFERROR(((C2:H2={"MIAOUT";"MIA";"UPTO"})+(-1*(C2:H2={"";"<>MIAOUT";"<>MIA";"<>UPTO"}))),FALSE))


It was the offsetting ranges causing the goof. The DAYOFF and HOLOFF were unnecessary, too.

Here's another.

=MAX(FREQUENCY(IF(C2:I2={"MIAOUT";"MIA";"UPTO"},COLUMN(C2:I2)),IF(C2:I2={"";"<>MIAOUT";"<>MIA";"<>UPTO"},COLUMN(C2:I2))))

Array entered.

Very impressive by FR and Jerry, I'm not good at this kind of problem. Just for kicks I put in some random to test the functions against each other. I then realized that I don't even understand the original problem logic to start with so I have no clue what's right or wrong. I hope OP shows up soon.