Arbeitszeiterfassung mit Excel / 9 Saldoberechnungen

Zu den wichtigsten Aufgaben des Zeitkontos gehören neben der Erfassung der Kommen-/Gehen-Zeiten auch die Saldoauswertungen. Dieses Tabellenmodell berechnet sowohl die täglichen Überstunden oder Fehlzeiten wie auch die kumulierten Salden, sodass der Anwender stets einen aktuellen Überblick über den Stand seines Zeitkontos hat.

Arbeitszeiten und Sollzeiten berechnen

Für die Arbeitszeit als Differenz zwischen Arbeitsende und Arbeitsbeginn (abzüglich Pausenzeit) genügt eine einfache Subtraktion der Zellwerte. Damit die Formel aber auf alle, auch auf nicht ausgefüllte Zellen anwendbar ist, sollten Sie wieder mit WENN abprüfen, ob Arbeitsbeginn und -ende erfasst sind:

I13: =WENN(ODER(F13=;G13=);;G13-F13-H13)

Die Spalte Pausen gesamt wird mit der Pausenzeit aus dem Infoblatt belegt. Die Formel zeigt das Ergebnis nur, wenn ein Wert in den Spalten Arbeitsbeginn oder Arbeitsende eingetragen ist:

H13: =WENN(ODER(F13=;G13=);;PAUSE)

Für die Berechnung der Sollzeiten, die im Infoblatt von Wochentag zu Wochentag variieren können, verwenden Sie wieder eine SVERWEIS()-Funktion. Mit der Funktion TEXT wird zunächst der Wochentag aus dem Datum ermittelt, und diesen sucht die Funktion SVERWEIS im Bereich RZEITEN. Diesen Namen hatten Sie zuvor dem zweispaltigen Bereich für die Regelarbeitszeiten zugewiesen. Mit WENN() sichern Sie das Ergebnis wieder ab, es wird nur berechnet, wenn ein Zeitwert für Arbeitsbeginn oder Arbeitsende eingetragen wurde.

=WENN(ODER(F13=;;G13=);;SVERWEIS(TEXT(D13;"TTTT");RZEITEN;2;0))

Kumulierten Saldo berechnen

In der Spalte mit der Überschrift Saldo (kumuliert) werden die Differenzen aus SOLL und IST aufaddiert. :

Dazu brauchen Sie 2 Formeln: Die erste ermittelt die Summe aus dem Übertrag des Vormonats und dem Tagessaldo, die zweite Formel kumuliert die Tagessalden durchlaufend (s. Abb. 11).

L13: =WENN(VORMONAT<>;WENN(K13<>;VORMONAT+K13;VORMONAT);)

L14: =WENN(L13<>;L13+WENN(K14<>;K14);K14)

Abb. 11: Saldo kumuliert berechnen

Spezialformatierungen: Negativsalden rot und Stundenwerte

Mit Spezialzahlenformaten geben Sie Ihrer Tabelle den letzten Schliff: Eine bessere optische Wirkung haben Zahlen, wenn die negativen Werte rot eingefärbt sind. Zu diesem Zweck gibt es neben der Bedingungsformatierung ein altes, aber bewährtes Verfahren mit Farbcodes im Zahlenformat. Kumulierte Zeitwerte sind außerdem augenscheinlich fehlerhaft, wenn sie den Grenzwert 24 überschreiten. Mit dem Standardformat hh:mm wird eine Zeit nur zwischen 0:00 und 24:00 Uhr richtig angezeigt. Für mehr als 24 Stunden gibt es ein Spezialformat.

Um Zellen nach der Wertigkeit ihres Inhalts wahlweise mit schwarzer (für positive Zahlen) oder roter Schrift (für negative Zahlen) zu versehen, können Sie auf die bedingte Formatierung zurückgreifen oder alternativ auf die Farbcodierung im Zahlenformat. Letztere bietet sich in unserem Modell an, da die Bedingungsformate bereits für die optische Anzeige der Feiertage und freien Tage verwendet werden: Bereich: K13:L4 mit Zahlenformat: hh:mm;[Rot]-hh:mm

Wenn die Zeitwerte mehr als 24 Stunden betragen, also einen dezimalen Wert größer 1 annehmen, werden sie fehlerhaft angezeigt. Die Berechnung ist aber korrekt, das Zahlenformat lässt keine andere Anzeige zu. Der Effekt lässt sich einfach erklären: Ein Zeitwert, der größer ist als 24 Stunden oder dezimal 1, weist automatisch den nächsten Tag des Excel-Kalenders aus. 36 Stunden sind demnach der 2. Januar 1900, 12:00 Uhr. Excel rechnet jeweils 24 Stunden in Tage des Kalenders um. Das Spezial-Zahlenformat [h]:mm sorgt dafür, dass der Zeitwert in Stunden angezeigt wird, die Stundenangabe wird einfach in eckige Klammern gesetzt: [h]:mm;[Rot]-[h]:mm