Eingabe von Formeln

Nachdem die Arbeitszeitnachweistabelle erstellt und auch in die anderen Tabellenblätter eingefügt ist, fangen wir mit den wichtigsten Teil an, mit der Eingabe von Formeln.

Damit die Formeln die Sie in Tabellenblatt "Januar" eingeben, automatisch in andere Tabellenblätter "Februar bis Dezember" übertragen werden, klicken Sie auf Tabellenblatt "Januar" mit der linken Maustaste und anschließend halten Sie die "Strg Taste" gedrückt und klicken dann auf jedes Tabellenblatt mit der linken Maustaste. So markieren Sie alle Tabellenblätter und müssen nicht die Formeln einzeln kopieren.

 

Tabellenblatt / Zelle Formel

Tabellenblatt "Vorgabe"

Zelle B2

Wenn, Sie es schon vorher nicht eingegeben haben dann bitte jetzt das Jahr eingeben.

z.B. 2014

Tabellenblatt "Vorgabe"

Zelle B3

Geben Sie hier die Stunden ein, die Sie gemäß Vertrag täglich arbeiten müssen.

Wichtig für Zeiteingaben:
Tippen Sie nach den Stunden einen Doppelpunkt und geben Sie die Minuten immer zweistellig ein. Denn aus der Eingabe 7:3 anstelle von 7:30 würde Excel automatisch "07:03" machen.

Tabellenblatt "Vorgabe"

Zelle B4

Geben Sie hier die Gesamtlänge der Arbeitspausen pro Tag ein.  Sind Ihre Pausen jeden Tag anders, lassen Sie die Zelle leer.

Tabellenblatt "Januar"

Zelle D3

Geben Sie in die Zelle D3 des Januar-Blatts bei Bedarf den Überstunden-Saldo aus dem Vorjahr ein.

Hinweis: In allen anderen Monatsblättern übernimmt Excel mit einer Formel automatisch den Saldo aus dem Vormonatsblatt. Ändern Sie dort nichts in Zelle D3.

Tabellenblätter "Februar" bis "Dezember"

Zelle D3

Formel: =INDIREKT(TEXT(E1-1;"MMMM")&"!I3")

Tabellenblätter "Februar" bis "Dezember" markieren und dann die Formel eingeben.

Ab dem Februar-Blatt erscheint hier automatisch der Übertrag an Überstunden oder Fehlstunden aus dem Vormonat. Er wird auch in Zelle J3 bei der Summe der gesamten Über-/Fehlstunden berücksichtigt.

 

Tabellenblatt "Januar"

Zelle E1

Hier wird der Monat angezeigt.

Formel: =DATUM(F1;G1;1)

Klicken Sie mit der rechten Maustaste auf die Zelle E1 - Zellen formatieren - Kategorie:Benutzerdefiniert - Typ: geben Sie MMMM ein. So formatieren Sie die Zelle und es wird dann nur der jeweilige Monat angezeigt.

Tabellenblatt "Januar"

Zelle F1

Hier wird das Jahr angezeigt.

Formel: =Vorgabe!B2

Tabellenblatt "Januar"

Zelle G1

Wenn Sie die Tabellenblätter markiert haben, hier müssen Sie die Markierung aufheben. Klicken Sie einfach auf das Tabellenblatt "Vorgabe".

In die Zelle G1 tippen Sie die Nummer 1 ein. Die Nummer steht für Monat Januar.

Tabellenblätter "Februar" bis "Dezember"

Zelle G1

In die Zelle G1 des jeweiligen Tabellenblattes folgende Nummern:

  • Februar: 2
  • März: 3
  • April: 4
  • Mai: 5
  • Juni: 6
  • Juli: 7
  • August: 8
  • September: 9
  • Oktober: 10
  • November: 11
  • Dezember: 12

Anschließend markieren Sie wieder alle Tabellenblätter.

Tabellenblatt "Januar"

Zelle B10 bis B40

Formel: =TEXT(C10;"TTT")

Die Zellen B11 bis B40 können Sie ganz einfach durch ziehen der Zelle B10 bis B36 mit den weiteren Tagen füllen oder Sie markieren die Zelle B10 bis B40 und klicken dann auf "Start"  in der Gruppe "Bearbeiten" auf "Füllbereich" (kleiner blauer Pfeil) und dann auf "Unten".

Tabellenblatt "Januar"

Zelle C10 bis C40

Formel: =DATUM($F$1;$G$1;ZEILE()-9)

Tabellenblatt "Januar"

Zelle D10 bis D40

Formel:

=WENN(ODER(IDENTISCH(C10;Vorgabe!$D$2:Vorgabe!$D$51));"Feiertag";WENN(B10="Sa";"Wochenende";WENN(B10="So";"Wochenende";"")))

In der Spalte "Status" kennzeichnet Excel automatisch Feiertage und Wochenenden.

So geben Sie andere freie Tage ein, damit Excel die Stunden korrekt ermittelt:

  • Wenn Sie sich freinehmen, tippen Sie Urlaub ein. Excel setzt das Tagessoll auf Null.
  • Fehlen Sie krankheitsbedingt, geben Sie Krank ein. Excel setzt das Tagessoll auf Null.
  • Wenn Sie Überstunden abbauen (Zeitausgleich) oder unentschuldigt fehlen, geben Sie Fehlzeit ein. Wenn Sie nur einen Teil des Tages fehlen, tragen Sie zusätzlich Anfangs- und Endezeit in Spalte D und F ein.
  • Beim Eintrag "Fehlzeit" zieht Excel in Spalte I das Tagessoll automatisch vom Überstundenkonto ab.
  • Tage mit den Einträgen "Krank" oder "Fehlzeit" hebt die Tabelle automatisch andersfarbig hervor.

Wichtig: Bei normalen Arbeitstagen muss die entsprechende Zelle unter "Status" leer bleiben, da die Tabelle sonst falsch rechnet. Sie können die Spalte nicht für andere Infos wie etwa Geburtstagshinweise nutzen.

Tabellenblatt "Januar"

Zelle E10 bis E40

Zelle G10 bis G40

Geben Sie Werte für Anfangs- und Endezeiten ein, z.B. 8:15 in E und 18:30 in G.

Wenn Sie an Wochenenden oder Feiertagen arbeiten, geben Sie ebenfalls die Zeiten ein. Excel verbucht dies automatisch als Überstunden in Spalte I.

Wichtig: Wenn Sie fertig mit der Erstellung der Arbeitszeitnachweis Vorlage sind, geben Sie die Zeiten ein. Nicht vergessen, die Tabellenblätter sind markiert und alles was Sie in Tabellenblatt "Janaur" eingeben wird auch in andere Tabellenblätter übernommen.

Tabellenblatt "Januar"

Zelle F10 bis F40

Formel: =WENN(E10="";0;Vorgabe!$B$4)

Klicken Sie mit der rechten Maustaste auf die Zelle F10 - Zellen formatieren - Kategorie:Benutzerdefiniert - Typ: geben Sie [hh]:mm ein. So formatieren Sie die Zelle.

Sobald Sie eine Anfangs-Zeit eingeben übernimmt Excel hier die im Register "Vorgabe" festgelegte Pausenzeit.

Falls Sie doch einmal längere oder kürzere Pausen als die Vorgabe machen, tragen Sie die Pausenlänge selbst ein.

Tabellenblatt "Januar"

Zelle H10 bis H40

Formel: =WENN(E10="";0;WENN(G10>E10;G10-E10-F10;1+G10-E10-F10))

In Spalte H berechnet Excel die geleistete Arbeitszeit pro Tag automatisch.

Klicken Sie mit der rechten Maustaste auf die Zelle H10 - Zellen formatieren - Kategorie:Benutzerdefiniert - Typ: geben Sie [hh]:mm ein. So formatieren Sie die Zelle. Anschließend ziehen Sie die Formel in bis Zelle H40.

Tabellenblatt "Januar"

Zelle I10 bis I40

Formel:

=WENN(D10="Fehlzeit";Vorgabe!$B$3;WENN(D10="";Vorgabe!$B$3;))

Klicken Sie mit der rechten Maustaste auf die Zelle I10 - Zellen formatieren - Kategorie:Benutzerdefiniert - Typ: geben Sie [hh]:mm ein. So formatieren Sie die Zelle. Anschließend ziehen Sie die Formel in bis Zelle I40.

Hier erscheint die Sollarbeitszeit aus dem Register "Vorgabe" bei jedem Arbeitstag, sofern in der Spalte "Status" kein Eintrag steht, der das Tagessoll auf Null setzt (z.B. "Urlaub", "Krank" oder "Feiertag".

Bei inoffiziellen Feiertagen oder halben Arbeitstagen wie etwa Rosenmontag oder Weihnachten und Silvester ändern Sie die Sollarbeitszeit von Hand.
Ist der Tag komplett frei, tippen Sie in die entsprechende Zelle in Spalte H 00:00 ein, bei halben Abeitstagen die anteilige Arbeitszeit, etwa 3:45.

Tabellenblatt "Januar"

Zelle J10 bis J40

Formel:

=WENN(D10="Fehlzeit";H10-I10;WENN(E10="";0;WENN(H10<>0;H10-I10;)))

Klicken Sie mit der rechten Maustaste auf die Zelle J10 - Zellen formatieren - Kategorie:Benutzerdefiniert - Typ: geben Sie [hh]:mm ein. So formatieren Sie die Zelle. Anschließend ziehen Sie die Formel in bis Zelle J40.

Unter "Überstunden" gibt Excel die Überstunden oder Fehlstunden pro Tag aus. Fehlstunden haben ein vorangestelltes Minuszeichen

 

Wenn Sie alles richtig gemacht haben, sollte Ihre Tabelle wie folgt aussehen:

Arbeitszeitnachweis Tabelle

Arbeitszeitnachweis Tabelle

Auf der nächsten Seite, berechnen wir Ihre Arbeitsstunden, Überstd./Fehlstd. und Gehalt.