Tutorials

Tutorial: Berechnung von SLA und TAT unter Ausschluss von arbeitsfreien Zeiten in Excel

Melden

Hier ist ein ausführliches Tutorial, wie du die Bearbeitungszeit (Turnaround Time – TAT) oder Service Level Agreements (SLA) in Excel berechnest, während du Wochenenden, Feiertage und arbeitsfreie Stunden (z. B. Nachtzeit) ausschließt.

In der Geschäftswelt ist es entscheidend, die tatsächliche Arbeitszeit zwischen zwei Zeitstempeln zu kennen. Eine einfache Subtraktion der Daten reicht oft nicht aus, da sie Nächte und Wochenenden mitzählt.

1. Die Vorbereitung

Zuerst benötigen wir eine klare Struktur in deinem Excel-Sheet. Lege folgende Felder fest:

  • Arbeitsbeginn (Startzeit): z. B. 08:00
  • Arbeitsende (Feierabend): z. B. 17:00
  • Feiertagsliste: Eine Liste mit Daten (z. B. in den Zellen F2:F10), die als arbeitsfrei gelten sollen.

2. Die Beispieldaten

Nehmen wir an, deine Daten stehen in folgenden Zellen:

  • Zelle A2: Start-Datum & Uhrzeit (z. B. 01.09. 09:00)
  • Zelle B2: End-Datum & Uhrzeit (z. B. 03.09. 14:30)
  • Zelle E1: Arbeitsbeginn (08:00)
  • Zelle E2: Arbeitsende (17:00)

3. Die universelle Formel

Um die Netto-Arbeitszeit in Stunden zu berechnen, nutzen wir eine Kombination aus der Funktion NETTOARBEITSTAGE und einer Zeitberechnung für den ersten und letzten Tag.

Kopiere diese Formel in deine Zielzelle:

=(NETTOARBEITSTAGE(A2;B2;F$2:F$10)-1)*(E$2-E$1)
+MEDIAN(REST(B2;1);E$1;E$2)
-MEDIAN(REST(A2;1);E$1;E$2)

4. Erklärung der Formel-Logik

  1. *`(NETTOARBEITSTAGE(A2;B2;F$2:F$10)-1)(E$2-E$1)`**:

    • Zählt die vollen Arbeitstage zwischen Start und Ende (minus den heutigen Tag).
    • Multipliziert diese Tage mit der täglichen Sollarbeitszeit (z. B. 9 Stunden).
    • Wochenenden und die Feiertage im Bereich F2:F10 werden automatisch abgezogen.

  2. REST(B2;1):

    • Isoliert die Uhrzeit aus dem Datum-Zeit-Stempel des Endes.

  3. MEDIAN(Uhrzeit; Arbeitsbeginn; Arbeitsende):

    • Dies ist ein Excel-Trick! Der Median stellt sicher, dass Zeiten außerhalb der Geschäftszeiten (z. B. wenn jemand um 22:00 Uhr fertig wird) auf die offizielle Feierabendzeit begrenzt werden. Das verhindert falsche Berechnungen bei Überstunden.

  4. Zusammenführung:

    • Die Formel addiert die Stunden der vollen Tage und verrechnet die Differenz zwischen der Start-Uhrzeit und der End-Uhrzeit innerhalb des Arbeitszeitfensters.

5. Das richtige Format (Wichtig!)

Damit Excel das Ergebnis korrekt als Stunden anzeigt, musst du die Zelle formatieren:

  1. Mache einen Rechtsklick auf die Ergebniszelle.
  2. Wähle Zellen formatieren.
  3. Gehe zu Benutzerdefiniert.
  4. Gib folgenden Code ein: [h]:mm

Warum die eckigen Klammern [h]? Normalerweise springt Excel nach 24 Stunden wieder auf 0 zurück. Die eckigen Klammern erzwingen, dass Excel die Stunden summiert (z. B. 45:30 statt 21:30).

Pro-Tipps für die Fehlervermeidung

  • Feiertage: Achte darauf, dass deine Feiertagsliste im Bereich F2:F10 wirklich nur Daten (ohne Uhrzeit) enthält.
  • Start/Ende außerhalb der Geschäftszeit: Wenn ein Ticket am Sonntag erstellt wurde, rechnet die MEDIAN-Funktion automatisch ab Montag 08:00 Uhr (Arbeitsbeginn).
  • Andere Wochenend-Konfigurationen: Falls dein Unternehmen nicht Samstag/Sonntag frei hat, nutze die Funktion NETTOARBEITSTAGE.INTL anstelle von NETTOARBEITSTAGE. Dort kannst du per Parameter (z. B. "0000011" für Mo-Fr Arbeit) festlegen, welche Tage Wochenende sind.

Zusammenfassung

Mit dieser Methode hast du eine robuste SLA-Berechnung, die völlig dynamisch reagiert. Du musst lediglich das Start- und Enddatum eingeben, und Excel liefert dir die exakte Netto-Bearbeitungszeit in Stunden und Minuten.

0