Tipps und Tricks rund um Excel

A	B	C	D	E	F	G	H	I/J	K
L	M	N	O	P/Q	R	S	T	U-W	X-Z

 

Zeitaufschrieb

1. Berechnung von Datum, Monat und Kalenderwoche
2. Feiertage einfügen
3. Formatierung
4. Berechnung der Stunden und Summen
5. Die nächsten Jahre

Mit den Grundfunktionen, die ich in Kalender selber bauen beschrieben habe, können Sie auch ein weitgehend automatisiertes Arbeitszeit-Blatt erstellen.

Ziel ist, für das nächste Jahr nur noch sehr wenig Arbeit zu haben: Einteilung in Monate und Wochen soll automatisch erfolgen, die Stunden sollen nur für Arbeitstage summiert werden.

1. Berechnung von Datum, Monat und Kalenderwoche

Herausgekommen ist diese Tabelle. Startpunkt ist die Zelle C2, in die der erste Tag des jeweiligen Jahres eingetragen wird. Ab C3 errechnet sich der Folgetag ganz simpel mit =C2+1. Spalte D wiederholt das Datum aus C (=C2) und formatiert es als Tag (TTT).

In den Spalten A und B wird der Monat bzw. die Kalenderwoche nur jeweils am Beginn angezeigt. Beginnen wir mit dem Monat. In A2 habe ich den Monat aus dem Datum in C2 mit =C2 und dem Format MMM übernommen.

Ab C3 prüfe ich, ob der Monat im Datum noch identisch mit dem Vortag ist.

=WENN(MONAT(C3)=MONAT(C2);"";C3)

Wenn der Monat in C3 identisch ist mit dem Monat aus C2, lasse die Zelle leer. Ist der Monat nicht identisch, liegt ein Monatswechsel vor - also übernimm das Datum aus C3. Da die Zelle als Monat mit MMM formatiert ist, wird nach jedem Wechsel des Monats im Datum der Monatsname abgekürzt geschrieben.

Die Kalenderwoche wird nur am ersten Tag einer Woche geschrieben. Also ermittel ich den Tag im Datum.

Wochentage werden in Deutschland mit Montag als erstem Tag gezählt - das ist die Option 2 in der Funktion WOCHENTAG.

Die Berechnung sieht so aus:

=WENN(WOCHENTAG(C3;2)=1;KALENDERWOCHE(C3;21);"")

Nur für den ersten Tag habe ich ganz simpel =KALENDERWOCHE(C2;21) verwendet, weil der erste Januar an einem Mittwoch ist und ich trotzdem eine "1" davor haben wollte. Man darf auch einfach "1" tippen ...

Wenn der Wochentag aus C3 dem Tag 1 entspricht, schreibe die Kalenderwochennummer des Datums aus C3. Sonst schreibe nichts. Auch für die Kalenderwochenzählung gibt es verschiedene Systeme. In Deutschland ist die erste Woche die Woche, in der die ersten vier Tage liegen (dazu mehr hier).

Alle Formeln werden immer nach unten kopiert bis zum letzten Tag es Jahres.

2. Feiertage einfügen

Die Feiertage kennt Excel nicht, sie lassen sich also nicht automatisch irgendwie einfügen. Ich habe eine Hilfstabelle erstellt - sie kann auf dem gleichen Blatt oder auf einem anderen Tabellenblatt liegen.

Die Tabelle wird von A2 bis B13 markiert und mit "Formeln / Namen definieren" benannt als Feiertage2014. Achtung! Sie müssen das jeweilige Datum mit Jahr eingeben: 01.01.2014 oder 06.01.2014.

In Spalte E wird mit einem SVERWEIS der Feiertag ermittelt. Die einfachste Formel wäre:

=SVERWEIS(C2;Feiertage2014;2;FALSCH)

Suche nach dem Datum aus C2 in der Matrix "Feiertage2014" und gib den Inhalt der zweiten Spalte aus. Da wir eine genaue Übereinstimmung suchen, muss das letzte Argument "FALSCH" lauten. Leider würde ich damit die Spalte mit dem Fehler #NV füllen, was ich hässlich finde. Darum ergänze ich die Formel so, dass im Falle eines Fehlerwertes nichts geschrieben wird:

=WENN(ISTFEHLER(SVERWEIS(C2;Feiertage2014;2;FALSCH))=WAHR;"";
SVERWEIS(C2;Feiertage2014;2;FALSCH))

Wenn Sie die Tabelle für das kommende Jahr verwenden, müssen Sie die Feiertagsliste für 2015 erstellen und benennen (Feiertage2015). Denken Sie daran, dass Sie die Datumsangaben wieder mit Jahr schreiben müssen - also nicht einfach die Feiertagsliste vom Vorjahr kopieren! Anschließend korrigieren Sie im SVERWEIS den Namen der Matrix auf "Feiertage2015".

3. Formatierung

In der Tabelle gibt es vier bedingte Formatierungen.

Die erste Formatierung soll die Wochenenden einfärben, die zweite die Feiertage. Bedingung drei und vier ziehen die Linien für das Ende einer Woche und eines Monats.

Regel 1: Färbe die Wochenenden ein

Markieren Sie die Tabelle ab Spalte C - bei mir ist das C2 bis I366 - rufen Sie die bedingte Formatierung auf. Sie brauchen "Formel zur Ermittlung der zu formatierenden Zelle verwenden".

Die Fomel lautet: =WOCHENTAG($C2;2)>5 Achten Sie darauf, dass Sie nur die Spalte absolut setzen! Wählen Sie als Formatierung z. B. eine Füllfarbe.

 

Regel 2: Färbe die Feiertage ein

Es wird wieder die Tabelle ab Spalte C markiert und eine neue bedingte Formatierung mit "Formel zur Ermittlung der zu formatierenden Zelle verwenden" erstellt.

Die Formel lautet: =$E2<>"" Auch hier wieder darauf achten, dass die Spalte absolut ist.

Mit $E2<>"" prüfen Sie, ob die Zelle E2 (und alle folgenden Zellen in der Spalte E) leer sind. <> steht für ungleich und die beiden Anführungszeichen für "leer".

Als Formatierung wählen Sie z. B. eine etwas dunklere Füllung.

Regel 3: Füge einen Strich am Anfang eines Monats ein

Jetzt geht die Markierung ab Spalte A, es wird wieder eine "Formel zur Ermittlung der zu formatierenden Zelle verwenden" erstellt.

Die Formel lautet: =$A2<>"" Auch hier wieder darauf achten, dass die Spalte absolut ist.

Als Formatierung verwenden Sie einen Rahmen oberhalb.

Regel 4: Füge einen dünnen Strich nach jeder Woche ein.

Diese Markierung beginnt in Spalte B, es wird wieder eine "Formel zur Ermittlung der zu formatierenden Zelle verwenden" erstellt.

Die Formel lautet: =$B2<>"" Auch hier wieder darauf achten, dass die Spalte absolut ist.

Als Formatierung verwenden Sie einen dünnen oder einen gepunkteten Rahmen oberhalb.

Kontrollieren Sie alle Formate und die zuständigen Bereiche. Rufen Sie dazu mit "Start | Bedingte Formatierung | Regeln verwalten" den Dialog auf.

Rechts sehen Sie die Bereiche, für die Regeln jeweils gelten. Sie können direkt in die Zeile bei "Wird angewendet auf" klicken, um Korrekturen vorzunehmen.

 

4. Berechnung der Stunden und Summen

In die Spalten F und G wird die Uhrzeit im Format hh:mm eingetragen. Die Pause wird ebenfalls im Uhrzeitformat erfasst - für eine Stunde trägt man 1:00 ein, für eine halbe Stunde 0:30.

Ich rechne aber letztendlich lieber mit dem Dezimalformat für Uhrzeiten. Die Formel lautet eigentlich ganz simpel:

=(G2-F2-H2)*24

Das wird formatiert als Zahl mit zwei Nachkommastellen.

Aber ich möchte erreichen, dass nicht über alle Tage inklusive arbeitsfreier Feiertagen und Wochenenden eine "0,00" angezeigt wird. Also prüfe ich, welcher Wochentag in Spalte C angezeigt wird und ob es in Spalte einen Feiertag gibt.

=WENN(UND(WOCHENTAG(C2;2)<6;E2="");(G2-F2-H2)*24;"")

Wenn der Wochentag in C2 Montag bis Freitag ist (C2 kleiner 6) bzw. in E2 nichts steht (E2 ist gleich leer), dann berechne die Stunden, sonst schreibe nichts. Die Formel wird nach unten ausgefüllt von I2 bis I366.

Ähnlich geht es für die Summe am Ende einer Woche. Am Beispiel der Zelle J11 sieht die Formel so aus:

=WENN(WOCHENTAG(C11;2)=5;SUMME(I7:I11);"")

Wenn der Wochentag 5 ist (Freitag), dann addiere die fünf darüber liegenden Zellen aus Spalte I.

Da ich in der ersten Kalenderwoche keine fünf Tage habe, addiere ich dort nur mit einer simplen Summe. Ab Kalenderwoche 2 erstelle ich die Formel in Zelle J11 zum ersten Mal und fülle es dann nach unten aus über alle Zellen. Die Formel steht in jeder Zelle von J11 bis J366.

5. Die nächsten Jahre

• Kopieren Sie das Tabellenblatt 2014 und benennen Sie es 2015.
• Erstellen Sie die Feiertagsliste für 2015 und geben Sie der Liste den Namen "Feiertage2015". Denken Sie daran, dass Sie die Datumsangaben neu eingeben müssen und dass das Jahr eingeschlosse sein muss: 01.01.2015, 06.01.2015 und so fort.
• Tippen Sie in C2 den 01.01.2105 ein.
• Ändern Sie die Formel in E2 auf
  =WENN(ISTFEHLER(SVERWEIS(C2;Feiertage2015;2;FALSCH))=WAHR;""; SVERWEIS(C2;Feiertage2015;2;FALSCH))
  Kopieren Sie die Formel nach unten bis zum letzten Tag des Jahres.

 

22.05.2014