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.