Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Arbeitsstundenzettel - Zähle Urlaubstage ohne Feiertage und WE
#1
Hallo zusammen,

ich erstelle mir gerade einen einfachen Arbeitsstundenzettel, indem ich mir auch meine Urlaubstage und Feiertage anzeigen lassen möchte.
Dabei möchte ich das Excel mir per Formel automatisch für den ausgewählten Monat die möglichen Urlaubstage ermittelt und davon die möglichen Feiertage und Wochenenden abzieht.
Die daraus resultierenden Tage würde ich mir dann in Stunden umrechnen und von meinen monatlichen Gesamtarbeitsstunden abziehen.

Was ich bisher als Lösungen gefunden hatte, waren die klassischen Erfassungsmethoden, bei dem ich entweder ein U oder F etc. eintrage und das zähle.
Das möchte ich aber in der Form nicht machen.

In der angehängten XLS Datei habe ich das mal soweit vorbereitet.

In der Zelle C3 kann ich mir über eine Dropdown Liste einen Monat auswählen.
Dadurch werden dann von A6:B36 für den jeweiligen Monat die entsprechenden Tage aufgelistet.
Auf diesen Bereich habe ich auch eine bedingte Formatierung definiert, welche mir die Urlaubstage und Feiertage farblich anzeigt.

In dem Bereich N10:N34 trage ich meine Feiertage oder sonstigen freien Tage ein. Wobei hier nicht alle Zellen gefüllt sein müßen.
in dem Bereich J21:K34 trage ich den Anfang und das Ende meines Urlaubes ein. Auch hier muß nicht der ganze Bereich gefüllt sein.

In der Zelle K11 soll nun die Anzahl an Urlaubstagen stehen, bereinigt von den Feiertagen aus N10:N34 und den Wochenenden.

Das zählen von Feiertagen in einem Monat ohne Wochenende funktioniert soweit in Zelle K9.
Die bereits schon erwähnte bedingte Formatierung funktioniert soweit auch. Berücksichtigt aber in der Berechnug keine Wochenenden.
Damit Wochenenden nicht farblich als Urlaub oder Feiertag hervorgehoben werden, habe ich die entsprechenden Regeln in Ihrer Position angepasst.

Da mein Excelwissen nicht so tiefgreifend ist, fehlt mir jetzt der Ansatz, wie ich das lösen könnte.
Läßt sich das in einer Formel abbilden?

VG Jens


Angehängte Dateien
.xlsx   Test Stundenzettel.xlsx (Größe: 28,18 KB / Downloads: 10)
Antwortento top
#2
Hallo,

mit Hilfsspalte in Spalte L (siehe Beispielmappe) und Nettoarbeitstage und Summenformel in K11.
Die Hilfsspalte L kannst du ja ausblenden.

Gruß Werner


Angehängte Dateien
.xlsx   Test Stundenzettel.xlsx (Größe: 29,17 KB / Downloads: 6)
Antwortento top
#3
Hallo,

danke erstmal für die Antwort.
Leider funktioniert Dein Beispiel so nicht.

Wenn ich in der Beispieldatei den April nehme, habe ich zb. 6 Urlaubstage bereinigt von WE und Feiertagen. Das funktioniert mit der Funktion Nettoarbeitstage in der Spalte L korrekt.
Ich kann aber in der Zelle K11 nicht einfach die Summer bilden über die Spalte L weil ja da auch die Urlaubstage gezählt werden, die nicht im April liegen.

Die Funktion in Spalte L müßte so angepasst werden, dass nur der ausgewählte Monat berücksichtigt wird.
Da fehlt mir halt der Ansatz wie ich das lösen könnte.

Schön wäre auch, wenn es ohne Zusatzspalten gehen würde.

VG Jens
Antwortento top
#4
Hallöchen,

dazu brauchst Du mehrere Fallunterscheidungen.

Im Prinzip

= Wenn (Beginn im aktuellen Monat, Wenn (Ende im aktuellen Monat, dann Ende - Beginn, sonst Monatsende - Beginn). sonst wenn (Ende im aktuellen Monat, Ende - Monatsanfang, ""))

Das deckt aber noch nicht den Fall ab, dass ein Urlaub 3 Monate tangiert. z.B. 5 Wochen Urlaub vom 31.8. bis 2.10.20 Da bräuchte man noch die Fallunterscheidung das Beginn im Vormonat und Ende im Folgemonat ist.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#5
Hallo,

Dein Ansatz klingt entweder nach einer sehr langen Formel, oder läßt sich gar nicht in einer Fromel unterbringen? Da müßte ich einmal ein bisschen probieren.

Ich hatte mir da gestern auch nochmal Gedanken drüber gemacht und mit einer Formel angefangen, da hat Excel aber dann den gesamten Urlaub ausgerechnet, Monatsübergreifend.

=SUMME(WENNFEHLER(WENN(AusgewählterMonat=MONAT(UrlaubsbeginZeile1);NETTOARBEITSTAGE.INTL(UrlaubsbeginZeile1;UrlaubsendeZeile1;1;Feiertage);0);0);WENNFEHLER(WENN(AusgewählterMonat=MONAT(UrlaubsbeginZeile2);NETTOARBEITSTAGE.INTL(UrlaubsbeginZeile2;UrlaubsendeZeile2;1;Feiertage);0);0);WENNFEHLER(WENN(AusgewählterMonat=MONAT(UrlaubsbeginZeile3);NETTOARBEITSTAGE.INTL(UrlaubsbeginZeile3;UrlaubsendeZeile3;1;Feiertage);0);0)) usw.

Da ich ja eigentlich nur die Urlaubstage vom ausgewählten Mionat haben möchte, habe ich da einen Gedankefehler, wenn ich bei meiner obigen Formel schaue, ob das Urlaubsende Datum > AktuellerMonat ist und dann das Monatsende vom aktuellen Monat als Urlaubsende nehme?
Ein Haken hätte die Sache, wenn der Urlaub Monatsübergreifend ist würde das für den Folgemonat nicht mehr berechnet. Muß ich wohl noch etwas herum probieren.

VG Jens
Antwortento top
#6
Hallöchen,

Zitat:habe ich da einen Gedankefehler, wenn ich bei meiner obigen Formel schaue, ob das Urlaubsende Datum > AktuellerMonat ist und dann das Monatsende vom aktuellen Monat als Urlaubsende nehme
in die Richtung geht das Smile Hier mal ein Teil. Du müsstest jeweils noch einen Tag dazu addieren, 20.-31. tangiert ja 12 Tage und das andere 11. Das Beispiel ist ohne Abzug WE + Feiertage usw, da müsstest Du noch NETTOARBEITTAGE verdrahten. Ist übrigens in der Regel besser, eine komplexe Formel in Teilstücken zu entwickeln und dann die funktionierenden Lösungen zusammenzusetzen als alles in einem Rutsch zu versuchen.

Arbeitsblatt mit dem Namen 'Tabelle1'
ABC
120. Okt14. Nov11
220. Okt30. Okt10

ZelleFormel
C1=WENN(MONAT(B1)>MONAT(A1);DATUM(JAHR(A1);MONAT(A1)+1;1)-1-A1;B1-A1)
C2=WENN(MONAT(B2)>MONAT(A2);DATUM(JAHR(A2);MONAT(A2)+1;1)-1-A2;B2-A2)
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

... noch ein Hinweis

So was

WENN(MONAT(B1)>MONAT(A1);DATUM(JAHR(A1);MONAT(A1)+1;1)-1-A1;B1-A1)

kann man mit MIN (oder am Monatsanfang mit MAX) auch vereinfachen

=MIN(B1;DATUM(JAHR(A1);MONAT(A1)+1;1))-A1

Statt -A1 dann prinzipiell der MAX,

... und noch einer

die Monatszahl habe ich hier aus dem Datum genommen. Du müsstest natürlich die Monatszahl nehmen, die für das Blatt steht, also

=MIN(B1;DATUM(JAHR(A1);MONAT(Blattmonat)+1;1))-A1

Du müsstest dann nur noch prüfen, ob der Blattmonat >= Beginn und <= Ende ist.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#7
Hallo,

danke für den Ansatz. Da werde ich mal schauen, dass ich das alles, auch erstmal in Teilstücken, in eine Formel bekomme.
Da ich nicht so intensiv mit den Funktionen arbeite bin ich immer für eine Tipp dankbar - hier mit MIN / MAX.
Werde ich mal ausprobieren.

VG Jens
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste