Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Überschneidungen und Pausenzeiten herausrechnen

Betrifft: Überschneidungen und Pausenzeiten herausrechnen von: Tanja
Geschrieben am: 13.08.2014 10:45:09

Hallo Forum,

leider habe ich wohl ein etwas größeres Problem. Ich habe eine Tabelle mit Ausfallzeiten. Die Zeiten können sich überschneiden oder auch in Pausenzeiten liegen. Ich benötige allerdings nur die Ausfallzeit, die tatsächlich in der Betriebszeit liegt und eine Ausfallzeit ohne Überschneidung. Die Berechnung soll sich nur auf den gefilterten Bereich beziehen. In der Beispieldatei ist gelb markiert ein Beispiel für eine Ausfallzeit die zum Teil in der Pause liegt und von der Datenbank direkt richtig berechnet ausgegeben wird (Spalte C, allerdings als Text ausgegeben, daher Achtung!). Blau markiert beispielhaft Überschneidungen + Pausenzeit.

Meine Idee für die Überschneidungen wäre eine Schleifenabfrage mit den vorhergehenden Zeilen, jedoch habe ich hier keine Ahnung wie ich die Pausenzeiten herausrechne, geschweige denn wie ich das ganze in VBA programmieren könnte. Bisher arbeite ich in meinem Makro hauptsächlich mit dem Macrorecorder.

Das Ergebnis der "realen" Ausfallzeit soll in Spalte D ausgegeben werden, allerdings nur in der letzten Zeile der Überschneidung.

Es ist schwer zu erklären, daher hoffe ich dass ich mein Problem gut genug dargestellt habe. Über eine Idee von jemandem wäre ich sehr dankbar!

Beispieldatei:
https://www.herber.de/bbs/user/92068.xlsx

Liebe Grüße und danke,

Tanja

  

Betrifft: AW: Überschneidungen und Pausenzeiten herausrechnen von: Michael
Geschrieben am: 15.08.2014 14:29:14

Hallo Tanja,

ich verstehe nicht ganz, warum "die Datenbank" im gelben Bereich richtig rechnet, in der ersten Zeile des blauen nicht. In beiden Fällen liegt der Beginn der Pause vor dem Beginn der Ausfallzeit und das Ende der Pause vor dem Ende der AZ.

Wenn man das programmtechnisch lösen will, muß man hier irgendeinen Unterschied feststellen können, aber ich sehe keinen, wo man das Programm "einrasten" lassen könnte - außer vielleicht der Betrachtung ALLER blauen Zeilen zusammen, aber wo anfangen?

Genau das wolltest Du wahrscheinlich wissen...

Sind die Pausenzeiten jeden Tag gleich?

Ich bin mir nicht sicher, ob das zum Ziel führt, aber als erstes wäre ich versucht, die Pausenzeiten in die Tabelle der Ausfallzeiten zu kopieren und dort mitzusortieren (z.B. neu eingefügte Spalte A mit Kennzeichen "AZ" bzw. "P"), dann hätte man die Angaben "beieinander" (suchen in mehreren Tabellen ist ja grundsätzlich keine große Sache, aber...), das könnte das Problem womöglich etwas griffiger machen: nur um zu verstehen, worum es geht.

Schöne Grüße,

Michael


  

Betrifft: AW: Überschneidungen und Pausenzeiten herausrechnen von: Tanja
Geschrieben am: 20.08.2014 16:20:10

Hallo Michael,

auch dir danke für deine Antwort. Die blau hinterlegten Zeiten habe ich manuell als Beispiel eingetippt und daher nicht darauf geachtet. Die Hilfspalte wäre tatsächlich eine Option, schöner wäre es natürlich ohne ;)

Liebe Grüße

Tanja


  

Betrifft: AW: Überschneidungen und Pausenzeiten herausrechnen von: fcs
Geschrieben am: 15.08.2014 14:40:29

Hallo Tanja,

hier mein Vorschlag für eine Makrolösung.
Sie funktioniert, wenn Start- und Ende-Zeit jeweils am gleichen Kalendertag sind.

Gruß
Franz

'Code in einem allgemeinne Modul
Sub prcRealDuration()
    Dim wks As Worksheet
    Dim Zeile As Long, Zeile_L As Long
    Dim ZeitStart As Date, ZeitEnde As Date
    Set wks = ActiveSheet 'Tabellenblatt mit Auflistung der Start- und Ende-Zeiten
    
    With wks
        Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
        For Zeile = 10 To Zeile_L 'Startzeile ggf. anpassen
            If .Rows(Zeile).Hidden = False Then
                'nur sichtbare Zeilen berücksichtigen
                ZeitStart = .Cells(Zeile, 1).Value
                ZeitEnde = .Cells(Zeile, 2).Value
                If .Cells(Zeile + 1, 1) > ZeitEnde Or Zeile = Zeile_L Then
                    'Keine Überschneidung
                    .Cells(Zeile, 4).Value = fncRealDuration(ZeitStart, ZeitEnde)
                Else
                    'Überschneidung - Zeile suchen bis Startzeit > vorherige Ende-Zeit
                    Do
                        Zeile = Zeile + 1
                        If .Rows(Zeile).Hidden = False Then
                            'nur sichtbare Zeilen berücksichtigen
                            If Zeile = Zeile_L Or .Cells(Zeile + 1, 1) > ZeitEnde Then
                                If .Cells(Zeile, 2) > ZeitEnde Then ZeitEnde = .Cells(Zeile, 2)
                                .Cells(Zeile, 4).Value = fncRealDuration(ZeitStart, ZeitEnde)
                                Exit Do
                            Else
                                If .Cells(Zeile, 2) > ZeitEnde Then ZeitEnde = .Cells(Zeile, 2)
                            End If
                        End If
                    Loop
                End If
            End If
        Next Zeile
    End With
End Sub

Function fncRealDuration(Start, Ende)
    Dim arrPausen As Variant
    Dim intPause As Integer
    Dim UhrzeitStart, UhrzeitEnde
    Dim dblSum As Double
    'Datum aus Start und Ende herausrechnen für Vergleich mit Pausenzeiten
    UhrzeitStart = CDate(Format(Start, "hh:mm:ss"))
    UhrzeitEnde = CDate(Format(Ende, "hh:mm:ss"))
    arrPausen = Worksheets("Pausen").Range("A3:B10")
    dblSum = Ende - Start
    For intPause = 1 To UBound(arrPausen)
        If UhrzeitStart > arrPausen(intPause, 2) Or UhrzeitEnde < arrPausen(intPause, 1) Then
            'Zeitram liegt außerhalb Pausenzeit, kein Abzug
        ElseIf UhrzeitStart >= arrPausen(intPause, 1) And UhrzeitEnde <= arrPausen(intPause, 2)  _
Then
            'Pause liegt komplett im Zeitraum - ganze Pause abziehen
            dblSum = dblSum - (arrPausen(intPause, 2) - arrPausen(intPause, 1))
        ElseIf UhrzeitStart < arrPausen(intPause, 1) And UhrzeitEnde > arrPausen(intPause, 1)  _
Then
            'im Zeitraum überschneidet mit Beginn der Pause
            dblSum = dblSum - (UhrzeitEnde - arrPausen(intPause, 1))
        ElseIf UhrzeitStart < arrPausen(intPause, 2) And UhrzeitEnde > arrPausen(intPause, 2)  _
Then
            'im Zeitraum überschneidet mit Ende der Pause
            dblSum = dblSum - (arrPausen(intPause, 2) - UhrzeitStart)
        End If
    Next
    fncRealDuration = dblSum
End Function



  

Betrifft: AW: Überschneidungen und Pausenzeiten herausrechnen von: Tanja
Geschrieben am: 20.08.2014 16:15:32

Hallo Franz,

vielen vielen Dank für deine Mühe. Ich habe bei dem ersten Test deines Makros bemerkt, dass keine Werte ausgegeben werden? Habe auch beim drüberschauen übers Makro keine Ausgabeanweisung gefunden. Liege ich damit richtig? Muss mich selbst noch in dein Makro näher reinarbeiten um es zu verstehen. Als Laie ist sowas ja immer schwieriger.

Wie gesagt, großen Dank für deine Mühe!

Liebe Grüße

Tanja