find the longest period of without gaps

Question

I need to create a formula that will show me the longest period that some employee has worked without interruption.

expected outpud is: - Agent1: - 21 days (10 shifts at 8h hours, 5 shifts of BO (ilness); 6 shifts of VK (weekend)

I need a formula or VBA (i cant use manual check because there is about 140 emplees that i should check :))

what do the input data look like? What have you tried? – Mladen Savic Feb 03 '17 at 14:40 — Mladen Savic, Feb 03 '17 at 14:40

score 0 · Answer 1 · answered Feb 06 '17 at 09:35

VBA solution

Assuming everything besides values '8', 'BO' and 'VK' are considered an interruption, this should provide the total number of days of the longest period of uninterrupted work:

Option Explicit

Function longestPeriod(rng As Range) As Integer
    Dim k As Integer, cell As Range
    k = 0
    longestPeriod = 0
    For Each cell In rng
        If cell.Value = "8" Or cell.Value = "BO" Or cell.Value = "VK" Then
            k = k + 1
        Else
            If k > longestPeriod Then
                longestPeriod = k
            k = 0
            End If
        End If
    Next cell
    If k > longestPeriod Then
        longestPeriod = k
    End If
End Function

Usage example: =longestPeriod(B6:B500)

find the longest period of without gaps

1 Answers1