Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Nachtstunden ermitteln und Lohn errechnen

Gruppe

Arbeitszeit

Problem

Die Arbeitszeit soll gem. den Daten in Spalte J in Normal- und Nachtstunden gesplittet werden. Der Lohn ist auf dieser Grundlage zu ermitteln.

Lösung
Geben Sie die nachfolgende benutzerdefinierte Funktion in ein Standardmodul ein.

StandardModule: Modul1

 Type PeriodStartStop
    Start As Double
    Stop_ As Double
  End Type

  Function ShiftHours(InTime As Date, OutTime As Date, _
    Optional ShiftStart As Date, _
    Optional ShiftEnd As Date) As Double
    Dim i As Long
    Dim j As Long
    Dim N As Double
    Dim Shift() As PeriodStartStop
    Dim Worked() As PeriodStartStop

    'if worked hours don't span midnight, there's just one period; if do
    'span midnight, will break into 2 periods, before and after midnight
    GetPeriods CDbl(InTime), CDbl(OutTime), Worked()

    'ditto for the shift
    GetPeriods CDbl(ShiftStart), CDbl(ShiftEnd), Shift()

    'get overlap of each worked period with each shift period
    N = 0
    For i = 0 To UBound(Worked())
      For j = 0 To UBound(Shift())
        N = N + GetOverlap(Worked(i), Shift(j))
      Next j
    Next i

    ShiftHours = Round(N * 24, 5)

  End Function

  Private Sub GetPeriods(ByVal t1 As Double, ByVal t2 As Double, _
    Period() As PeriodStartStop)
    'NB: t1 and t2 are time values 0 <= t < 1, not hours

    t1 = t1 - Int(t1)
    t2 = t2 - Int(t2)

    'does period span midnight?
    If t1 <= t2 Then
      'no: just one period
      ReDim Period(0 To 0)
      Period(0).Start = t1
      Period(0).Stop_ = t2
    Else
      'yes: split into 2 periods: t1 to midnight, midnight to t2
      'note: in 1st period midnight = 1, in 2nd period, midnight = 0
      ReDim Period(0 To 1)
      Period(0).Start = t1
      Period(0).Stop_ = 1
      Period(1).Start = 0
      Period(1).Stop_ = t2
    End If
  End Sub

  Private Function GetOverlap(Period1 As PeriodStartStop, _
    Period2 As PeriodStartStop) As Double
    Dim t1 As Double
    Dim t2 As Double

    'NB: Stop_ must never be less than Start, which is
    'ensured by splitting periods that span midnight
    'into 2 periods

    'overlap, if any, is from later start to earlier end
    'get the later of the start times
    If Period1.Start >= Period2.Start Then
      t1 = Period1.Start
    Else
      t1 = Period2.Start
    End If

    'get the earlier of the Stop_ times
    If Period1.Stop_ <= Period2.Stop_ Then
      t2 = Period1.Stop_
    Else
      t2 = Period2.Stop_
    End If

    'subtract start from stop to get length of interval
    'if result is positive, this is length of overlap
    'if result = 0, one period starts when the other ends
    'if result < 0, the periods don't overlap; result
    '  is the size of the gap between periods;

    'since we are only interested in overlap,
    'change negative result to 0

    t2 = t2 - t1
    If t2 < 0 Then t2 = 0

    GetOverlap = t2
  End Function

    

Beiträge aus dem Excel-Forum zu den Themen DatumZeit und Arbeitszeit