Negative Arbeitszeitenn Summieren

[Paul1206]

Hallo Miteinander,

hier muss ich mir leider Asche auf Haupt streuen (ist mir total peinlich).

Ich habe einfach nicht über den Tellerrand raus gedacht, dass in der Summe mehr als 24h rauskommen und dadurch ein Datum und Zeit entsteht.

Die Variablen timeSoll und timeIst müssen auf Double dimensioniert werden und die Zelle *24 gerechnet werden. In der Rückgabe muss das Ergebnis /24 geteilt werden und die Zielzellen müssen [hh]:mm formatiert werden.

Wenn du natürlich leere Zellen in deiner Tabelle hast kann nicht addiert werden. Deshalb der Fehler.

Ulrich möchte natürlich, dass du auch mal selbst den/die Fehler behebst.

Hier deshalb erst mal nur der von mir übersehene Teil korrigiert, damit die Summenausgabe über 24h funktioniert.

Code: Alles auswählen

Private Sub SummenAZ(ByVal FahrerSpalte As String, _
                        ByVal SollSpalte As String, _
                        ByVal IstSpalte As String, _
                        ByVal GesamtFahrer As String, _
                        ByVal GesamtSoll As String, _
                        ByVal GesamtIst As String)
    
    Dim Ws As Object, i&, j&, k&, arr(), timeSoll#, timeIst#, Treffer As Variant
    ReDim arr(1 To 1000)
    For Each Ws In ThisWorkbook.Sheets
        If IsDate(Ws.Name) Then k = k + 1: arr(k) = Ws.Name
    Next
    ReDim Preserve arr(1 To k)
    With Tabelle3
        For i = 2 To Tabelle3.Cells(Rows.Count, GesamtFahrer).End(xlUp).Row
            For j = LBound(arr) To UBound(arr)
                Treffer = Application.Match(.Cells(i, GesamtFahrer), Sheets(arr(j)).Columns(FahrerSpalte), 0)
                If Not IsError(Treffer) Then
                    timeSoll = timeSoll + (Sheets(arr(j)).Range(SollSpalte & Treffer) * 24)
                    timeIst = timeIst + (Sheets(arr(j)).Range(IstSpalte & Treffer) * 24)
                End If
            Next j
            .Cells(i, GesamtSoll) = timeSoll / 24: timeSoll = 0
            .Cells(i, 9) = timeIst / 24: timeIst = 0
        Next i
    End With
    SummeZeiten
End Sub

Vielleicht noch ein Tipp:
Prüfe den Zellinhalt auf <>"" vor der Additon der Zeiten von timeSoll und timeIst.

Gruß Uwe

[d'r Bastler]

Moin allerseits,

anonymisiert kannst du alles hier hochladen, ansonsten auf gar keinen Fall!

Im Bekanntenkreis als Don Quichote des Datenschutzes verschrien, erlaube ich mir dennoch folgenden Nachsatz: anonymisiert ist definitiv immer besser! Aber: Auf den VBAsteleien.de gibt es die Möglichkeit reale Anhänge in den PNs zu teilen.

Es bleibt dennoch ein Risiko. Denn Arbeitgeber bzw. Betriebsräte (mit denen man so etwas aber durchaus auch abstimmen kann) können diesbezüglich ziemlich eklig werden.

Schönen Am'd noch!

[knobbi38]

...
Auf den VBAsteleien.de gibt es die Möglichkeit reale Anhänge in den PNs zu teilen.
...
Es bleibt dennoch ein Risiko. Denn Arbeitgeber bzw. Betriebsräte (mit denen man so etwas aber durchaus auch abstimmen kann) können diesbezüglich ziemlich eklig werden.

Das ist schön, wenn man über PNs Dateien austauschen kann, diese Funktion habe ich schon in anderen Forum vermißt, aber i.d.R. ist bei personenbezogenen Daten der Empfänger nicht berechtigt, solche Daten zu erhalten bzw. zu lesen und könnten durchaus Ärger bekommen, ähnlich wie unbedarfte Anwender, welche ein Datenleck in einer Firma oder Organisation entdecken und melden. Denen drohen u.U. z.Zt. noch strafrechtliche Konsequenzen, aber das ist ein anderes Thema.
Das man ein solches Vorgehen mit Arbeitgebern und Betriebsräten abstimmen kann, halte ich nicht für möglich, weil diese dafür keine Berechtigung erteilen können. Es muß dann schon ein entsprechendes vertragliches Verhältnis zum Empfänger bestehen, daß die entsprechenden datenschutzrechtlichen Bestimmungen regelt, z.B. im Rahmen eines Werkvertrages.

Also besser nur anonymisierte Daten - immer - und sei es nur zum Schutz von Sender und Empfänger.

Gruß Knobbi38

[Hobbit]

Moin,
ich habe hier schon hin und her gedoktert, aber ich bekomme es einfach nicht hin das er die Summe von Minus und Plus Zeiten richtig in J 32 summiert.

Zeiten.jpg

Ich weiß echt nicht was hier verkehrt ist.

Code: Alles auswählen

Sub SummeZeiten()
    Dim i&, sumZeiten, tmp
    With Tabelle3
        For i = 2 To 30
            If Left(.Cells(i, 10), 1) = "-" Then
                sumZeiten = sumZeiten - CDate(Mid(.Cells(i, 10), 2, 5)) * 1
            Else
                sumZeiten = sumZeiten + CDate(.Cells(i, 10)) * 1
            End If
        Next i
        .Range("J32") = IIf(sumZeiten < 0, Format(CDate(sumZeiten), "-hh:mm"), Format(CDate(sumZeiten), "hh:mm"))
    End With
End Sub

Kann mir da bitte einer Helfen?

Vielen dank für die Mühen.


Vielen dank

[knobbi38]

Hallo Jens,

den entscheidenden Tipp hast du schon in #11 bekommen. Zeiten und Daten werden intern als Double gespeichert und diese kannst du problemlos addieren. Wenn du das Vorzeichen nicht berücksichtigen möchtest, könntest du auch ABS() verwenden; Stringverarbeitung bei Zahlen ist eher kontraproduktiv.

Also: Werte in Double konvertieren (nicht Date!), mit 24 multiplizieren und dann addieren. Das Ergebnis wieder durch 24 teilen und die Ausgabe entsprechend im Zeitformat formatieren.

Bilder sind ja immer ganz schön, helfen aber nicht weiter, weil man nicht erkennen kann, was für Datentypen deine Range enthält. Besser kleines Upload mit deiner Problemstellung, sonst wird das nichts.

Gruß Knobbi38

[Hobbit]

Moin und vielen dank für die Tipps, aber ich habe versucht aus Cdate , Cdbl zu machen, aber es klappt nicht.
Ich weiß nicht wie das mit dem Double funktioniert.
Ich komme einfach nicht weiter.

Kann mir da bitte einer Helfen?

Vielen dank für die Mühen.

[knobbi38]

Hallo Jens,

ein Beispiel sagt manchmal mehr als tausend Worte. Die bei dir grün dargestellten Werte sind natürlich kein Double/Date Werte, sondern vom Typ String, und dieser muß natürlich etwas anders behandelt werden. Um nun eine korrekte Summe zu bilden, kannst du diesen Code verwenden:

Code: Alles auswählen

Public Sub SummeZeiten()
  Dim i As Long, timeSum As Double
  Dim avnt() As Variant
  
  avnt = Range("J2:J30").Value
  
  For i = 1 To UBound(avnt)
    If VarType(avnt(i, 1)) = vbString Then
      ' timeSum = timeSum - CDbl(TimeValue(Replace(avnt(i, 1), "-", "")))
      timeSum = timeSum - CDbl(CDate(Mid$(Trim$(avnt(i, 1)), 2)))
    Else
      timeSum = timeSum + avnt(i, 1)
    End If
  Next
  
  Range("J32").Value = timeSum
End Sub

Anstatt TimeValue() könntest du in diesem Beispiel auch CDate() verwenden, das Ergebnis wäre identisch. Da beide Funktionen nicht mit negativen Vorzeichen in einer Zeitangabe umgehen können, muß dieses vorher mit Replace gelöscht werden. Alternativ geht das natürlich auch mit den einfachen Stringfunktionen. Ich habe dir mal beide Möglichkeiten hier kodiert.
Das ganze ist etwas vereinfacht unter der Annahme, daß nur negative Zeitangaben als String kodiert sind, ansonsten müßte man gezielt das Vorzeichen auswerten, was ich mir hier geschenkt habe.

Gruß Knobbi38

[Paul1206]

Hallo Ulrich,

es ist damit zu rechnen, dass es hier auch zu negativen Summen kommt.
Da ich nur mit dem Tablett bis vorhin mal hier nachschauen konnte und jetzt erst wieder am PC bin, schau die mal meinen Lösungsvorschlag an.

Code: Alles auswählen

Sub SummeZeiten()
    Dim i&, sumZeiten
    With Tabelle3
        For i = 2 To 30
            If Left(.Cells(i, 10), 1) = "-" Then
                sumZeiten = sumZeiten - CDate(Mid(.Cells(i, 10), 2, 5)) * 24
            Else
                sumZeiten = sumZeiten + CDate(.Cells(i, 10)) * 24
            End If
        Next i
        .Range("J32") = IIf(sumZeiten < 0, Format(CDbl(sumZeiten) / 24, "-hh:mm"), CDbl(sumZeiten) / 24)
    End With
End Sub

Function SUMMEZEIT(Bereich As Range)
    Dim i&, sumZeiten
    With Bereich
        For i = 1 To .Rows.Count
            If Left(.Cells(i, 1), 1) = "-" Then
                sumZeiten = sumZeiten - CDate(Mid(.Cells(i, 1), 2, 5)) * 24
            Else
                sumZeiten = sumZeiten + CDate(.Cells(i, 1)) * 24
            End If
        Next i
    End With
    SUMMEZEIT = IIf(sumZeiten < 0, Format(CDbl(sumZeiten) / 24, "-hh:mm"), CDbl(sumZeiten) / 24)
End Function

Einzig die Zelle muss [hh]:mm formatiert werden.
So braucht es die 1904 Datumseinstellung nicht. Mich würde deine Meinung dazu interessieren.

Gruß Uwe

[knobbi38]

Hallo Uwe,

ich habe jetzt mal auf die Vorzeichenabfrage verzichtet und nur den Datentyp als Kriterium genommen, das sollte in diesem Fall reichen. Zur Sicherheit kann man die Werte mit 24 multiplizieren und dann wieder dividieren, das sollte jetzt kein Problem sein.
Aus Geschwindigkeitsüberlegungen bevorzuge ich die Arrayvariante, also die Range in ein Array konvertieren und dieses dann für die Berechnungen verwenden. Die Variable für die Summenberechnung würde ich noch als Double deklarieren; Berechnungen mit Variablen vom Datentyp Variant sind eher langsam und CDate liefert intern ohnehin einen Double-Wert, also kein großer Akt für die implizite Typkonvertierung.
Wenn wirklich negative Werte herauskommen können, ist die abschließende Formatierung sicherlich angebracht, vielleicht so:

Code: Alles auswählen

    Dim sumZeiten#
    
    With Bereich
    ...
    End With
    
    sumZeiten = sumZeiten / 24
    
    If sumZeiten < 0 Then
      SUMMEZEIT = WorksheetFunction.Text(Abs(sumZeiten), "\-[hh]:mm")
    Else
      SUMMEZEIT = WorksheetFunction.Text(sumZeiten, "[hh]:mm")
    End If

Die IIF Funktion in VBA bewertet immer alle Ausdrücke, deshalb habe ich diese Form mit IF - THEN - ELSE gewählt.

Grüße Ulrich

[Hobbit]

Moin,

vielen dank für die tolle Hilfe.

Schönes Wochenende.