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. Beispiel: 2021 |
| 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 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 | n 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: =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 bis zur 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 bis zur 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:
Auf der nächsten Seite, berechnen wir Ihre Arbeitsstunden, Überstd./Fehlstd. und Gehalt.
Hallo ich mache an manchen Tagen Minusstunden. Dabei werden jedoch in Spalte J nur ##### angezeigt. Wie kann ich das da einstellen dass die angezeigt werden?
Die Spalte ist zu schmal, einfach breiter machen
Bitte um Hilfe
Zuerst einmal „Danke“, für die Bereitstellung dieser Tabelle.
Die Tabelle funktioniert wunderbar,bis auf das unten beschrieben Problem. (Schade)
Der Übertrag von D3 funktioniert nicht!
Im Monat Februar steht beim Übertrag nur #BEZUG! Und bei
Überstd. / Fehlstd. (Monat) seht auch nur #BEZUG!.
Ab dem Monat März steht bei Übertrag 0 Und bei
Überstd. / Fehlstd. (Monat) auch 0.
Ich habe mich genau an die Anweisungen gehalten und es geht trotzdem nicht.
Kann mir bitte jemand helfen, dass wäre „Super“.
Vielen Dank im Voraus, Manne.
Hallo,
ich habe eine vertraglich festgelegte Monatsarbeitszeit. Daraus ergibt sich für jeden Monat eine unterschiedliche tägliche Arbeitszeit. Kann mir jemand helfen, wie ich das in den Arbeitszeitnachweis hier einbaue? Bin schon recht weit gekommen mit der Anleitung, aber daran haperts jetzt. Würde das ungern umsonst gemacht haben- ich hoffe es gibt eine Möglichkeit, das einzubauen.
viele Grüße
Hallo
ich habe alles übernommen wie beschrieben aber die Feiertage werden nicht übernommen. Nur die Wochenenden stehen in der Spalte Status
Hallo
die vorlage ist sehr gelungen was sie hier ins netz gestellt haben ABER ich hab ein kleines problem mit den Feiertagen. ich hab alles genauso gemacht wie sie er zeigt mich die wochenenden an nur die feiertage eben nicht was kann ich jetzt tun??
danke für die info
Mfg christian
Leider lassen sich die Feiertage nicht von der Vorlage in das Monatsblatt übertrage mit der Formel. Was mache ich da falsch?
Besteht auch die Möglichkeit die Urlaubstage zu integrieren?
liebe Grüße und danke
Hallo! Ich habe jetzt alle Tricks ausprobiert, um den „Feiertag“ in meine Tabellen zu bekommen – nichts funktioniert. Habe alles von Hand eingegeben und auch die Matrix definiert. Ich bekomme immer nur #WERT.
Hallo, Sie müssen die Zelle anders formatieren. Sie müssen [hh]:mm als Format für die Zeit angeben. So können mehr als 24 Stunden dargestellt werden.