Excel – Stundenzettel – Arbeitstage eines Monats anzeigen

Ein Stundenzettel soll für jeweils einen Monat erstellt werden. Dabei gilt es, lediglich die potentiellen Arbeitstage aufzulisten.

Wird an Montagen und Sonntagen nicht gearbeitet, sollen diese Tage deaktiviert werden können. Zudem gibt es eine Liste mit Ausnahmen für generelle Feiertage.

601a

Hier bietet sich die Funktion ARBEITSTAG.INTL an. Diese Funktion ermittelt ausgehend von einem Startdatum ein Zieldatum. Dieses Zieldatum wird aber in reinen Arbeitstagen Abstand berechnet und nicht in Kalendertagen. INTL am Funktionsnamen sagt zudem an, dass die freien Wochentage selbst definiert werden können.

Hier ein Bild der Ergebnisdatei aus Video Excel # 604:

604b

Die folgenden Funktionen und Eingaben wurden hier getätigt:

Zelle B1 – beinhaltet das Datum des anzuzeigenden Monats. Hier kann z.B. jeweils der Monatserste angegeben werden.

Bereich G2:G8 – hier stehen 0-Werte für Arbeitstage und 1-Werte für Nicht-Arbeitstage.

Zelle G9 – hier werden die Zahlen aus G2:G8 verkettet. Das kaufmännische Und-Zeichen fügt die Werte zu einem Textstring zusammen. Formel =G2&G3&G4&G5&G6&G7&G8
Hier hätte man natürlich auch die Funktion =VERKETTEN(G2;G3;G4;G5;G6;G7;G8) nehmen können.

Bereich H2:H9 – hier werden die Feiertage eingegeben. Die Liste müsste eigentlich noch viel länger werden. Tage wie Allerheiligen, Ostern und Weihnachten werden zusätzlich zu Nicht-Arbeitstagen nicht berücksichtigt.

Die folgende Formel wurde in B5 verfasst und bis B35 nach unten kopiert:
=WENN(MONAT(ARBEITSTAG.INTL($B$1-1;ZEILEN($B$5:B5);$G$9;$H$2:$H$9))=MONAT($B$1);ARBEITSTAG.INTL($B$1-1;ZEILEN($B$5:B5);$G$9;$H$2:$H$9);"")
Diese Funktion wird nach unten kopiert. Dabei vergrößert sich der Bereich $B$5:B5 in der Folgezeile zu $B$5:B6 bis schließlich zu $B$5:B35.

Zusammen mit der Funktionen ZEILEN wird z.B. mit =ZEILEN($B$5:B8) ermittelt, wie viele Zeilen der Bereich von Zeile 5 bis Zeile 8 umfasst. Hier also 4 Zeilen. Wir haben somit einen Zähler, der mit jeder Zeile automatisch um den Wert 1 größer wird.

Somit wird ausgehend vom Startdatum in Zelle B1 zunächst ein Zähler abgezogen, um im selben Moment in Zelle B5 sofort wieder einen Zähler darauf zu zählen. Drei Zeilen tiefer werden aber schon 4 Werte dazu gezählt und so weiter.

Die Funktion =ARBEITSTAG.INTL(Ausgangsdatum;Tage;Wochenende;freie_Tage) zählt von einem Startdatum eine gewisse Anzahl von Tagen drauf. Dabei werden aber die unter Wochenende aufgeführten Muster ausgelassen und einfach weiter gezählt. Zudem werden auch die freien Tage einfach ausgelassen, so dass man beim Weiterzählen der Tage nur Arbeitstage und deren Datumswerte bekommt.

Damit nicht über den Monat hinaus gezählt wird, wird mit =Monat(B1) die Monatszahl des Zielmonats ermittelt und mit den Ergebnissen von ARBEITSTAG.INTL verglichen. Sind beide identisch, wird der ARBEITSTAG tatsächlich eingetragen. Ansonsten kommt der WERT „“, somit erfolgt keine Eingabe eines Datums.

Dieses Thema wird in weiteren Videos und Blog-Einträgen fortgesetzt. Jetzt gibt es zumindest schon einmal eine Liste der relevanten Datumswerte.

Hier ist die Datei mit dem finalen Stand zum Abschluss des Videos (Rechtsklick und Ziel speichern unter):
Excel604_Ergebnisdatei

Hier geht es zum 1. Teil der Videoreihe

Videolink: http://youtu.be/uFIXx8RN4qA

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 )

Twitter-Bild

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

Facebook-Foto

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

Google+ Foto

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

Verbinde mit %s