1.1k Aufrufe
Gefragt in Tabellenkalkulation von
Guten morgen zusammen,

ich bin noch nicht so gut, wenn es um Formeln in Excel geht und bei VBA bin ich noch blutiger Anfänger, deshalb benötige ich eure Hilfe.

Ich habe in Spalte B und Spalte C Zeitangaben im Format "dd.mm.yy hh:mm:ss". Von diesen Uhrzeiten (Start und Ende) habe ich bereits die Differenz und den Durchschnitt ausgerechnet.

Allerdings sollen bei diesen Berechnungen nur ein bestimmter Zeitraum betrachtet werden.
Zum Beispiel von 07:00 bis 17:00. Die Zeit zwischen 17:01 und 6:59 soll nicht mit berechnet werden.

Beispiel:
Start                       Ende
20.01.18 09:00:00    21.01.18 09:00:00
22.01.18 07:03:34    22.01.18 08:03:00
25.01.18 09:03:40    25.01.18 13:03:00

Aktuell zeigt es mir einen Durchschnitt von 9h und 40min an.
Mein Ziel wäre aber der Durchschnitt: 5h
Ich möchte also die Formel für die Differenzen so verändern, dass nur noch der Zeitraum zwischen 07:00 und 17:00 betrachtet wird.
Die Berechnung soll also nicht über die Arbeitszeit hinaus gehen und erst wieder am nächsten Arbeitstag weiter rechnen.

Ich würde mich freuen, wenn mir jemand weiterhelfen könnte.

Vielen Dank für eure Hilfe
MaryJo

11 Antworten

0 Punkte
Beantwortet von
Hallo MaryJo,

ich bin nicht sicher, ob ich dich richtig verstanden habe. Falls deine Daten in A1 und B1 beginnen probier mal für C1 die folgende Formel aus:

[b]=WENN(ABRUNDEN(A1;0)<>ABRUNDEN(B1;0);ABRUNDEN(A1;0)+ZEIT(17;0;0)-WENN(STUNDE(A1)<7;ABRUNDEN(A1;0)+ZEIT(7;0;0);WENN(STUNDE(A1)>=17;ABRUNDEN(A1;0)+ZEIT(17;0;0);A1))+WENN(STUNDE(B1)<7;ABRUNDEN(B1;0)+ZEIT(7;0;0);WENN(STUNDE(B1)>=17;ABRUNDEN(B1;0)+ZEIT(17;0;0);B1))-(ABRUNDEN(B1;0)+ZEIT(7;0;0));WENN(STUNDE(B1)<7;ABRUNDEN(B1;0)+ZEIT(7;0;0);WENN(STUNDE(B1)>=17;ABRUNDEN(B1;0)+ZEIT(17;0;0);B1))-WENN(STUNDE(A1)<7;ABRUNDEN(A1;0)+ZEIT(7;0;0);WENN(STUNDE(A1)>=17;ABRUNDEN(A1;0)+ZEIT(17;0;0);A1)))[/b]

Gruß Mr. K.
0 Punkte
Beantwortet von maryjo Einsteiger_in (17 Punkte)
Hallo xlKing,

erstmal vielen Dank für die Antwort. Ich hab nicht mehr damit gerechnet ^^.
Und vielen Dank für deine Formel, ich bin mit meinen Formeln einfach nicht weiter gekommen.

Eine Frage hätte ich noch:
Ist es auch möglich die Wochenenden(Samstag & Sonntag) ebenfalls aus der Berechnung auszuschließen?
Sodass nur die Zeit innerhalb der Werktage berechnet wird.

LG
MaryJo
0 Punkte
Beantwortet von
Hallo MaryJo,

ohne Hilfsspalten ist das leider nicht möglich, die dafür nötige Formel ist so lang, dass sie Excel nicht mehr verarbeiten kann. Falls du dich für Hilfsspalten entscheidest, wo du die bereinigten Datumsangaben berechnest, könnte deine Formel in [i]C1[/i] so
[b]=WENN(WOCHENTAG(A1;2)>5;ABRUNDEN(A1;0)+1/24*7+(8-WOCHENTAG(A1;2));ABRUNDEN(A1;0)+WENN(STUNDE(A1)>=17;1/24*17;WENN(STUNDE(A1)<7;1/24*7;A1-ABRUNDEN(A1;0))))[/b] und in [i]D1[/i] so
[b]=WENN(WOCHENTAG(B1;2)>5;ABRUNDEN(B1;0)+1/24*17-(WOCHENTAG(B1;2)-5);ABRUNDEN(B1;0)+WENN(STUNDE(B1)>=17;1/24*17;WENN(STUNDE(B1)<7;1/24*7;B1-ABRUNDEN(B1;0))))[/b] aussehen. Beides als Datum formatiert. Dann ließen sich in [i]E1[/i] die Std. berechnen: [b]=WENN(D1<C1;0;WENN(ABRUNDEN(D1;0)=ABRUNDEN(C1;0);D1-C1;D1-(ABRUNDEN(D1;0)+1/24*7)+(ABRUNDEN(C1;0)+1/24*17-C1)))[/b]

Das mag für deinen Fall ausreichen. So richtig zufriedenstellend ist das aber nicht, zumal die Formeln für Dritte immer noch recht unübersichtlich sind. Einfacher und korrekter wäre hier eine Benutzerformel über VBA, die viel mehr Möglichkeiten bietet. Gib dazu in einem Standardmodul den folgenden Code ein:

[code]Function Arbeitsstunden(FirstDay As Date, LastDay As Date, BeginOfWork As String, EndOfWork As String) As Date
  
  Dim std As Date
  
  If FirstDay < Int(FirstDay) + TimeValue(BeginOfWork) Then FirstDay = Int(FirstDay) + TimeValue(BeginOfWork)
  If LastDay > Int(LastDay) + TimeValue(EndOfWork) Then LastDay = Int(LastDay) + TimeValue(EndOfWork)
  If FirstDay > Int(FirstDay) + TimeValue(EndOfWork) Then FirstDay = Int(FirstDay) + 1 + TimeValue(BeginOfWork)
  If LastDay < Int(LastDay) + TimeValue(BeginOfWork) Then LastDay = Int(LastDay) - 1 + TimeValue(EndOfWork)
  If Weekday(FirstDay, vbMonday) > 5 Then FirstDay = Int(FirstDay) + 8 - Weekday(FirstDay, vbMonday) + TimeValue(BeginOfWork)
  If Weekday(LastDay, vbMonday) > 5 Then LastDay = Int(LastDay) - (Weekday(LastDay, vbMonday) - 5) + TimeValue(EndOfWork)
    
  If Int(FirstDay) = Int(LastDay) Then
    Arbeitsstunden = LastDay - FirstDay
  Else
    std = Int(FirstDay) + TimeValue(EndOfWork) - FirstDay + LastDay - Int(LastDay) - TimeValue(BeginOfWork)
    If LastDay > FirstDay + 1 Then
      std = std + (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * (Int(LastDay) - Int(FirstDay) - 1)
      std = std - Int((LastDay - FirstDay) / 7) * (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
      If Weekday(LastDay, vbMonday) < Weekday(FirstDay, vbMonday) Then std = std - (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
    End If
    Arbeitsstunden = std
  End If

End Function[/code]Damit bist du nun nicht nur die leidigen Hilfsspalten los sondern sogar in der Lage die Stunden auch für mehrere Tage anzuzeigen oder Arbeitsbeginn und -ende schneller neu zu definieren. Die Formel in Excel lautet dann schlicht:
[b]=arbeitsstunden(A1;B1;"7:00";"17:00")[/b]
Sollter der Fehler [i]#WERT![/i] ausgegeben werden, bedeutet das, dass der Zieltag (B1) selbst im bereinigten Zustand noch vor dem Starttag (A1) liegt. Eine Berechnung ist hier dann unsinnig. Den Fehler kannst du vermeiden indem du noch ein WENNFEHLER(...;0) drumrum baust. Probiers aber lieber erstmal ohne, damit du ein Gefühl für das jeweils richtige Ergebnis bekommst.

Gruß Mr. K.
0 Punkte
Beantwortet von
Achja: Die Zelle mit dem Ergebnis musst du natürlich noch so formatieren, dass du das Ergebnis auch lesen kannst. Zahlenformat: [hh]:mm:ss
0 Punkte
Beantwortet von
Noch ne kleine Korrektur: Der Fehlerwert #Wert! steht für falsche Eingaben in der Formel. Wenn das Zieldatum kleiner als das Startdatum ist wird 0 zurückgegeben.

Function Arbeitsstunden(FirstDay As Date, LastDay As Date, BeginOfWork As String, EndOfWork As String) As Date
  
  Dim std As Date
  
  If FirstDay < Int(FirstDay) + TimeValue(BeginOfWork) Then FirstDay = Int(FirstDay) + TimeValue(BeginOfWork)
  If LastDay > Int(LastDay) + TimeValue(EndOfWork) Then LastDay = Int(LastDay) + TimeValue(EndOfWork)
  If FirstDay > Int(FirstDay) + TimeValue(EndOfWork) Then FirstDay = Int(FirstDay) + 1 + TimeValue(BeginOfWork)
  If LastDay < Int(LastDay) + TimeValue(BeginOfWork) Then LastDay = Int(LastDay) - 1 + TimeValue(EndOfWork)
  If Weekday(FirstDay, vbMonday) > 5 Then FirstDay = Int(FirstDay) + 8 - Weekday(FirstDay, vbMonday) + TimeValue(BeginOfWork)
  If Weekday(LastDay, vbMonday) > 5 Then LastDay = Int(LastDay) - (Weekday(LastDay, vbMonday) - 5) + TimeValue(EndOfWork)
  
  If LastDay > FirstDay Then
    If Int(FirstDay) = Int(LastDay) Then
      Arbeitsstunden = LastDay - FirstDay
    Else
      std = Int(FirstDay) + TimeValue(EndOfWork) - FirstDay + LastDay - Int(LastDay) - TimeValue(BeginOfWork)
      If LastDay > FirstDay + 1 Then
        std = std + (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * (Int(LastDay) - Int(FirstDay) - 1)
        std = std - Int((LastDay - FirstDay) / 7) * (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
        If Weekday(LastDay, vbMonday) < Weekday(FirstDay, vbMonday) Then std = std - (TimeValue(EndOfWork) - TimeValue(BeginOfWork)) * 2
      End If
      Arbeitsstunden = std
    End If
  End If
  
End Function
0 Punkte
Beantwortet von maryjo Einsteiger_in (17 Punkte)
Hallo xlKing,

wenn ich die Formel =arbeitsstunden(A1;B1;"7:00";"17:00") eingebe, spuckt Excel mir einen Fehler aus, weil er angeblich nichts mit dem Namen anfangen kann.
Hast du eine Idee an was das liegen könnte?


Gruß MaryJo
0 Punkte
Beantwortet von
Hi MaryJo,

wie gesagt musst du zuvor den Code in ein allgemeines Modul der gleichen Arbeitsmappe kopieren. Sonst kann Excel die Formel nicht finden.
Die Arbeitsmappe muss als .xls oder .xlsm gespeichert werden, sonst löscht Excel beim Schließen den Code wieder.

Gruß Mr. K.
0 Punkte
Beantwortet von
Für den Fall, dass du noch nicht weißt wo der Code hin muss: Öffne den VBA-Editor (Alt + F11) und füge über Menü Einfügen ein Modul ein. Dort
kopierst du den Code rein (z.B. mit Strg + V)

Mr. K.
0 Punkte
Beantwortet von maryjo Einsteiger_in (17 Punkte)
Soweit bin ich schon^^
Also den Code hab ich schon eingefügt, deswegen ist mir auch nicht klar, wieso Excel den Namen nicht erkennt.

LG MaryJo
0 Punkte
Beantwortet von
Hast du vielleicht das Ausführen von Makros deaktiviert? Prüf mal deine Sicherheitseinstellungen.
...