Arbeitszeitberechnung, Nullwerte unterdrücken
Eine mehr oder weniger typische Arbeitszeit-Berechnung (ohne wesentliche Formatierung, ohne Pausen und hier sehr spartanisch) sieht beispielsweise so aus:
Bis auf die Überschrift fast unformatierte Tabelle
In Spalte D wird die Arbeitszeit mit folgender Formel berechnet: =C2-B2 und solange keine Eingaben gemacht werden, steht in Spalte D auch das Ergebnis, dass 0:00 Stunden gearbeitet worden sind. – Das mag ja noch angehen, denn es stimmt ja auch vom Prinzip her. Es kann aber irritieren, wenn auch an Wochenenden oder anderen freien Tagen dieser Wert dort steht. Gleiches gilt für Tage, die es in dem Monat gar nicht gibt.
So wirklich unschön wird es, wenn in der Frühe der Arbeitsbeginn schon eingegeben wird und (natürlich) das Ende noch offen ist:
Das Arbeitsende ist noch nicht eingetragen
Die berechnete Arbeitszeit wird als „Gartenzaun” dargestellt. Das ist im Prinzip auch logisch, denn eine leere Zelle wird als Null gewertet und 0:00–8:50 ergibt ein negatives Resultat. Und Negativ-Zeiten werden in Excel (Windows) grundsätzlich in dieser Form dargestellt. Mac-User kennen dieses Problem übrigens nicht. Es gibt verschiedene Wege, das mit den Null-Werten zu umgehen. Teils per Zahlenformat, über die bedingte Formatierung oder mit einer entsprechenden Formel.
Es existiert eine Datei für die Arbeitszeitberechnung, welche für diese Übung etwas modifiziert wurde. Laden Sie dieses File herunter, um die Erklärungen besser nachvollziehen zu können. Beachten Sie bitte: Die entsprechenden Änderungen beziehen sich ausschließlich auf Spalte E (Arbeitszeit). Andere Spalten sind in der Formatierung belassen worden, die für den entsprechenden Beitrag sinnvoll war. Keine Regel ohne Ausnahme, aber die kommt später und wird detailliert beschrieben.
Der Urzustand
Die Zeilen 6:17 sind so belassen worden, wie sie in der Ursprungsdatei vorhanden sind. Unter der Zeile 17 (und weiteren Zeilen) sind von mir Trennstriche eingefügt. Sie dienen ausschließlich der besseren Übersicht der „Blöcke”, welche den Monat in Arbeitsschritte für diese Übung unterteilen.
In den Zeilen 18:21 steht die typische, ganz einfache Formel; die einzige Formatierung besteht hier darin, dass eine Uhrzeit Typ hh:mm dargestellt wird. In E20 ist (natürlich) das Ergebnis 0:00, was nicht unbedingt stören muss. Allerdings: Es war der freie Samstag. Und in E21 wird deutlich, dass negative Zeiten in der Windows-Version nicht „sauber” dargestellt werden: ###. Und das hat natürlich nichts mit dem arbeitsfreien Sonntag zu tun … 😉 Ab Zeile 22 gibt es noch weitere Besonderheiten; darauf wird dann jeweils gezielt eingegangen.
Bedingte Formatierung
Eine durchaus probate Methode, Negativergebnisse und Nullwerte bei Zeiten unsichtbar zu machen, ist die Bedingte Formatierung. Der Bereich E22:E25 ist ein Beispiel dafür. Die Optik stimmt (erst einmal), die korrekten Ergebnisse stehen dennoch in den Zellen drin. – Das große Aber: Sie können den so behandelten Zellen keine individuelle Hintergrundfarbe „verpassen”. Und was vielleicht nicht so ganz gravierend ist: Manche Drucker haben die Möglichkeit, jede Farbe (einschließlich weiß) oder auch Texte schwarz zu drucken. Da kommt der „Schwindel” wieder zum Vorschein 😎 .
Formel-Lösung (1)
Wenn solch ein Problem per Formel gelöst wird, dann ist es in den meisten Fällen eine Lösung, welche dieser in E28 ähnelt: =WENN(B28<C28;C28-B28-D28;""). Das kann aber schief gehen, wenn eine weitere Formel auf das Ergebnis zugreift und eine Zahl erwartet. G28 und H28 zeigen das ganz deutlich auf. Und natürlich ist in den beiden Spalten auch in Zeile 37 der gleiche Fehlerwert. Übrigens: Die Meldung #WERT! besagt, dass ein Wert erwartet wird und ein solcher nicht gefunden wurde. Hier ist es der LeerString "", der ja ein Text der Länge null ist. Wäre die Zelle wirklich leer, dann würde die Zelle als Null gewertet aber dennoch leer dargestellt werden.
Formel-Lösung (2)
Ehe Sie sich Hoffnungen machen: Es bleibt beim LeerString als Ergebnis, mit all seinen Nachteilen. Aber die Formel ist vielleicht etwas transparenter und für Sie unter Umständen auch leichter anpassbar: =WENN(ANZAHL(B29:C29)>1;C29-B29-D29;""). Wenn also mindestens die beiden ersten Spalten mit Zahlen ausgefüllt sind, dann soll berechnet werden, sonst wird "" zurück gegeben.
Dennoch gibt es eine sinnvolle Formel-Lösung, wenn auch weiterführend. Dei Fehlermeldungen tauchten ja in den beiden Differenz-Spalten auf, also Spalten G:H. Dort können Sie nämlich auch ansetzen, um die Fehlermeldung zu unterdrücken. In G29 und auch H29 sehen Sie, dass die Formel eine andere ist als in der Zeile darüber. Hier steht (im Beispiel Spalte G): =WENN(E29<>"";(E29-F29)*24;"") und nicht nur die einfache Berechnung. Es wird erst abgefragt, ob der Inhalt der Spalte E kein LeerString ist; dann wird berechnet, sonst wird auch hier ein LeerString ausgegeben. Ein kleiner Umweg, aber eine durchaus tragbare Lösung.
Per Zahlenformat
Die wohl eleganteste Lösung ist, das Zahlenformat in den entsprechenden Bereichen anzupassen. Die Berechnungen erfolgen so, dass durchaus Nullwerte als Ergebnis herauskommen können. Aber das Zahlenformat ist so eingestellt, dass nur positive Werte angezeigt werden. Und das bedeutet, dass auch Negativ-Zeiten wie beispielsweise am 26. Februar in Spalte E nicht als „Lattenzaun” angezeigt werden.
Sehen Sie sich einmal das Zahlenformat in E30 an. Es ist ein benutzerdefiniertes Format und lässt nur positive Werte zu: hh:mm;;. Auch Nullen bzw. der Wert 0 als Ergebnis werden unterdrückt. Dennoch können mit dem Inhalt der Zelle weitere Berechnungen durchgeführt werden.
Wenn Sie diese Formatierung in die Zeilen 31:35 übertragen, werden Sie erkennen, was ich meine. Das Minus-Ergebnis vom 26. Februar wird unsichtbar, wie auch die restlichen Zeilen. Und schauen Sie sich einmal die Formel in E32 an. Sie ist fast mit jener in E28 identisch, aber eben nur fast. Statt des Leerstrings wird nun eine Null zurück gegeben. Und da die Null eine Zahl ist, kommt es trotz sonst gleicher Bedingungen nicht zur Fehlermeldung in den Spalten G:H.
Resümee
Idealerweise verwenden Sie eine Formel zur Berechnung, welche Ihnen 0 zurück gibt, wenn noch nicht alle Zeiten eingegeben worden sind oder nicht gearbeitet wurde. Das direkt hierüber aufgezeigte Zahlenformat sorgt für ein „besseres” Aussehen der Daten.
Soll zwingend eine Pause eingegeben werden, erforderlichenfalls auch 0:00, dann ist die Formel mit der ANZAHL()-Funktion besser einsetzbar. In dem Fall muss der Wert für die Anzahl der Werte auf >2 geändert werden. Ist dann keine Pausenzeit eingegeben, erfolgt keine Berechnung bzw. die Arbeitszeit wird auf 0:00 gesetzt. Mit einem WENN()-Konstrukt könnte hier auch ein Default-Wert eingesetzt werden.
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen … (← Klick mich!)