Excel – Uhrzeiten schreiben und formatieren (3)

Gestern ist die  Grundform mit den wichtigsten Berechnungen entstanden, heute kümmern wir uns um kosmetische Probleme und Feinheiten.

Kosmetisches Problem: ######

Die Formel ist nach unten ausgefüllt, aber die Uhrzeiten sind noch nicht alle eingetragen: dann sehen Sie in Spalte D als Ergebnis 00:00. Wenn Sie nur den Beginn eintragen, aber das Ende nicht, werden ##### angezeigt.

30.06.2013_ 0005_thumb[1]

Das können Sie vermeiden, wenn Sie die Berechnung in D abhängig von der Angabe einer Ende-Zeit machen.

=WENN(C2<>””;C2-B2;””)

30.06.2013_ 0006_thumb[1]

Das bedeutet im Klartext:

  • C2<>””Wenn C2 nicht leer ist
    <> bedeutet “ungleich” und die zwei Anführungszeichen “” steht für “leer”
  • C2-B2
    Dann rechne C2-B2
  • “”
    Sonst rechne nichts
    Die zwei Anführungszeichen “” stehen für leer oder nichts

 

Mittagspause

Jeden Tag soll von der Gesamtstundenzahl des Tages die Mittagspause abgezogen werden. Ergänzen Sie die Spalte E “Mittag” und tragen Sie hier die Zeit für die Mittagspause ein: 1:00 Stunde oder 0:30 Stunden für eine halbe Stunde.

30.06.2013_ 0011

Als Rest wird gerechnet D2-E2 (Gesamtstunden abzüglich Mittagspause). Wenn noch nicht alle Tage mit Beginn und Ende ausgefüllt sind, erhalten Sie eine Fehlermeldung #WERT! Darum soll wieder die Berechnung nur erfolgen, wenn in Spalte D ein Wert vorliegt. Zum Abfangen von Fehlermeldungen gibt es die Funktion WENNFEHLER. Sie lautet hier:

=WENNFEHLER(D3-E3;””)

Im Klartext: Berechne D3-E3, im Falle eines Fehlers schreibe nichts.

Die Zellen in Spalte F werden auch wieder als Uhrzeit im Format hh:mm formatiert.Die Summe unter allen Einträgen in F muss wieder das Format [h]:mm erhalten.

Gleitzeit – Plus und Minus

Bei einer täglichen Regelarbeitszeit von acht Stunden und der Möglichkeit zu “gleiten”, sammeln Sie eventuell Plus oder Minus an. Sie können das pro Tag anzeigen. Allerdings stoßen Sie jetzt auf ein Problem – wenn Sie weniger arbeiten als Sie müssen und Minuszeiten einkassieren, zeigt Excel ##### an. Excel kann negative Uhrzeiten nicht darstellen, weil es sie eigentlich nicht gibt.
Darum müssen Sie jetzt auf die dezimale Schreibweise ausweichen – also nicht 1:30 Stunden, sondern 1,5 Stunden.

Hinweis

Eine genauere Erklärung der Rechenwege finden Sie im Lexikon unter Minuszeiten und Dezimale Schreibweise von Uhrzeiten.

Schreiben Sie die Regelarbeitszeit in eine Zelle – ich habe mir über der Tabelle eine leere Zeile eingefügt und die Regelarbeitszeit in G1 geschrieben. Ergänzen Sie die Überschrift “Plus/Minus”. Die Berechnung ist:
=WENNFEHLER((F3-$G$1)*24;””)

30.06.2013_ 0012

Auch hier wird mit WENNFEHLER die Fehlermeldung #WERT! abgefangen, falls die Zellen in E leer sind.
Damit für alle Berechnungen die Regelarbeitszeit aus G1 verwendet wird, muss der Zellbezug absolut gesetzt werden – also $G$1. Die Multiplikation mit 24 sorgt für die dezimale Umrechnung. Formatieren Sie die Zelle als Zahl mit zwei Nachkommastellen. Füllen Sie jetzt die Formel nach unten aus.

Achtung! Wenn Sie als Zahlenformat “Standard” verwenden, bekommen Sie für die 0 eine sonderbare Exponential-Schreibweise. Darum das Format “Zahl”!

Ein Beispiel für die Berechnung von Schichtarbeitszeiten, die über Nacht gehen, finden Sie im Lexikon unter Schichtdienst.

Ausfüllen durch andere

Wenn Sie die Tabelle für Kollegen erstellen und andere die Listen ausfüllen, ist es praktisch, wenn Sie nur die tatsächlich auszufüllenden Zellen zum Beschriften frei geben. Sonst passiert es schnell, dass jemand die Formeln überschreibt und dann Berechnungen nicht mehr funktionieren.

Beschriftbar müssen sein die Spalten B, C und E. Alle anderen Spalten enthalten Berechnungen. Ob Sie G1 änderbar machen, hängt davon ab, wie flexibel die Regelarbeitszeit für die Mitarbeiter sind. Wenn jeder 8 Stunden Soll hat, muss niemand die Zahl ändern können.

30.06.2013_ 0013

Markieren Sie die Zellen, die geändert werden sollen. Wählen Sie “Start / Zahl / Zahelnformat” und das Register “Schutz”. Entfernen Sie das Häkchen bei “Gesperrt”.

30.06.2013_ 0014

Damit wird jetzt erreicht, dass alle Zellen gesperrt sind (das ist Standard bei Excel) mit Ausnahme der gerade markierten und “entsperrten” Zellen. Bestätigen Sie den Dialog.

Wählen Sie das Register “Überprüfen” und dort “Blatt schützen”. Vorher ist kein Schutz aktiv und alle Zellen können geändert und gelöscht werden!

30.06.2013_ 0015

Aktiviert sein muss:

  • Arbeitsblatt und Inhalt gesperrter Zellen schützen
  • Nicht gesperrte Zellen auswählen

Ob Sie die Auswahl gesperrter Zellen zulassen, ist Geschmacksfrage. Es passiert nichts dabei – der Anwender kann die nicht freigegebene Zelle zwar anklicken, aber nicht ändern oder löschen. Wenn Sie Ihre Formeln nicht zeigen wollen, können Sie dieses Häkchen herausnehmen – aber es ist besser, jeder sieht, welche Berechnungen zugrunde liegen.

Ein Kennwort ist nicht unbedingt notwendig. Der Schutz funktioniert auch ohne Passwort, allerdings kann dann jeder den Schutz einfach aufheben.

Jetzt können in das Blatt nur noch die von Ihnen freigegebenen Zellen verändert werden.

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s