Count Consecutive Days while bridging over days off and holidays

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.