Zeitkonten: Rechnen mit Stunden und Minuten

Eine Zeitkontenübersicht soll anzeigen, um wie viel Stunden man Soll-Werte unter- oder überschritten hat. Das Problem dabei: Excel kann negative Zeitwerte (1 ist der Startwert für Excel: dazu gehört der 1.1.1900; Werte vor diesem Datum stellt Excel nicht dar!)nicht darstellen:
Rauten

Die jeweiligen Formate der Spalten sind für diese Lösung in Reihe 2 angedeutet:

Loesung

Erfasst werden zunächst Arbeitszeiten. Die errechnete Arbeitszeit in Spalte C wird nun verglichen mit einem Sollwert in Spalte D. Die Abweichung vom Sollwert wird anschließend dezimal und in einem Zeitformat ausgegeben.

Berechnung und Summe in Spalte C

Leicht ist zunächst die Formel in C3:    =B3-A3

Da jedoch in Reihe 5 im Schichtbetrieb gearbeitet wird, kommt man hier in den negativen Bereich, den Excel wie oben gezeigt durch #### anzeigt. Hier muss man über eine WENN-Bedingung prüfen, ob der Wert in Spalte B größer oder gleich dem Wert in Spalte A ist: Ist die Bedingung erfüllt, rechnet man wie in dem für C3 angegebene Beispiel.

Ist aber die Bedingung nicht erfüllt, muss man 24 Stunden (+1) in der Formel addieren, um wieder in den positiven darstellbaren Bereich zu kommen:

C3

Damit die Summe in C7 (ebenso in Feld D7) über 24 Stunden hinaus rechnet, muss hier  das Format [h]:mm zugewiesen werden:

[H]MM

Die dezimale Differenz in Spalte E

E7 Für diese Werte zieht man die Zeitwerte ab und multipliziert mit 24, um diese Werte dezimal umzurechnen. Damit negative Werte in Rot mit Minuszeichen und zwei Nachkommastellen erscheinen, wählt man folgendes Zahlenformat:

Minutenformat

Die formatierte Ergebnisspalte F

Die Funktion ABS(E7/24) liefert den positiven (absoluten) Wert der Zahl  vom Ergebnis E7/24.

Die Funktion Text formatiert die Zahl nun so um, dass sie mit Stunden und Minuten erscheint. Ist der Wert in E7 kleiner als 0, wird über die Wenn-Bedingung noch ein Minuszeichen vorangestellt:

Formatiertes Ergebnis

Textfunktions negative Minuten

Damit jetzt negative Zahlen in Rot erscheinen, ist für die Ergebnisspalte eine bedingte Formatierung zugewiesen:

Bedingtes Stundenformat

Wenn im Text ein Minuszeichen gefunden wird, wird der Text rot formatiert.

-

Bei dieser Lösung stimmt der Rotton nicht mit dem Format in der Spalte davor überein. Dazu wählt man besser ein   benutzerdefiniertes Format: hellrot