Urlaubsplanung mit aufteilung der Tage in Monate

Computer: Software & Programmierung Office Software
#1

Hallo Excel-Freunde,
erst Mal wollte ich mich bedanken, für eure vielen hilfreichen Antworten und hoffe, dass Ihr mir bei meinem Problem auch helfen könnt.
Ich habe einen Urlaubsplan mit GANTT-Diagramm das auch ohne Probleme funktioniert. Jetzt benötigt aber unsere Abrechnungsabteilung die einzelnen Urlaubstage in Monate aufgeteilt und dies wiederrum in einer eigenen Tabelle.
Somit würden sich folgende Kriterien für eine Formel ergeben, die ich leider nicht hin bekomme:

  1. Suche nach dem Namen auf Sheet 1
  2. Wenn der Name gefunden worden ist, such in der gleichen Zeile nach den Urlaubstagen
  3. Teile dann die Tage in die entsprechenden Monate auf
  4. Wechsel dann auf Sheet 2
  5. Suche in dieser Liste nach dem entsprechenden Namen
  6. Fülle die Urlaubstage in die entsprechenden Monate ein
  7. Wenn mehrere einzelne Urlaubstage pro Monat genommen wurden, addieren alle mit einander
    Ich hoffe, dass so eine Formel zu erstellen ist, ich leider kein Makro verwenden kann.

Ich habe auch eine Beispieldatei, aber weiß leider nciht wie ich diese anhänge.

Danke schon Mal im Voraus

#2

Hallo Tom 28A,

ich hoffe du gestattest mir einige Anmerkungen die mit deiner eigentlichen Frage nichts zu tun haben. Aber am Ende kommt auch noch ein Versuch dir zumindest teilweise zu helfen.

1 Es ist sehr erfreulich zu sehen dass es doch noch User gibt die sich neu anmelden und bereit sind ihre Fragen vernünftig und freundlich zu stellen. Schon seit einiger Zeit ist es hier leider zur Gewohnheit geworden dass die Fragen von neu angemeldeten Usern ohne Anrede und Gruß, ohne nähere Infos, ohne Punkt und Komma, ohne Absätze und auch sonst nur dahin gerotzt geschrieben werden. All das ist „Gott sei Dank“ bei deiner Frage nicht der Fall gewesen. Das gibt einem den Glauben an den guten Willen der Hilfe suchenden wieder, dafür ist dir mein Dank gewiss.

2 Ich kenne mich zwar ein wenig mit Excel aus, aber deine Aufgabe ist für mich dann doch etwas zu komplex, da werde ich dir wohl nicht weiter helfen können. Allerdings gibt es hier im Brett echte Spezialisten, für die es wahrscheinlich ein leichtes sein dürfte dir zu helfen. Also deine Chancen stehen gut dass dir hier geholfen werden kann.

Ich habe auch eine Beispieldatei, aber weiß leider nciht wie ich diese anhänge

Auch das zeigt mir wieder dass du mitgedacht hast, und bereit bist bei der Lösung deines Problems mitzuhelfen. Leider ist selbst dass mittlerweile nicht mehr selbstverständlich. Deswegen möchte ich dir zumindest dabei helfen wie du deine Beispieldatei hier zur Verfügung stellen kannst.

Gehe auf diese Seite: http://www.file-upload.net/

Dort kannst du deine Beispieldatei hochladen. Nach dem hochladen werden dir verschiedene Links angezeigt. Den richtigen Link kopierst du, und fügst ihn in deinen Beitrag hier im Forum ein.

Siehe hier: http://www.abload.de/img/1qvll7.jpg

Dann kann man sich deine Beispieldatei herunter laden.

Gruß (und viel Erfolg)

N.N

#3

Hallo Excel-Freunde,

Hallo Tom28A,

Ich habe einen Urlaubsplan mit GANTT-Diagramm das auch ohne
Probleme funktioniert. Jetzt benötigt aber unsere

Sorry, was ist GANTT?

Abrechnungsabteilung die einzelnen Urlaubstage in Monate
aufgeteilt und dies wiederrum in einer eigenen Tabelle.
Somit würden sich folgende Kriterien für eine Formel ergeben,
die ich leider nicht hin bekomme:

  1. Suche nach dem Namen auf Sheet 1

Ist der Name ein benamter Bereich oder der Name des Mitarbeiters?
Sind die Namen statisch oder unterliegen diese ständiger Änderung?

  1. Wenn der Name gefunden worden ist, such in der gleichen
    Zeile nach den Urlaubstagen

Werden die Urlaubstage fortlaufend in einen bestimmten Zellbereich eingetragen? Sind das nur einzelne Tage, oder ggf. auch mehrere Tage von bis?

  1. Teile dann die Tage in die entsprechenden Monate auf

Bei einzelnen Tagen wäre das Monat(Datum). Bei einem Zeitraum wirds fummelig.

  1. Wechsel dann auf Sheet 2
  2. Suche in dieser Liste nach dem entsprechenden Namen

Also geht es um Mitarbeiter namen.

  1. Fülle die Urlaubstage in die entsprechenden Monate ein

In etwa Monat X, Tage Y?

  1. Wenn mehrere einzelne Urlaubstage pro Monat genommen
    wurden, addieren alle mit einander
    Ich hoffe, dass so eine Formel zu erstellen ist, ich leider
    kein Makro verwenden kann.

Was spricht gegen ein Makro? Muss die Abrechnung nicht sehen. Kann in Deiner persönlichen Arbeitsmappe liegen. Wiederkehrende Aufgaben bestimmter Muster schreit geradewegs nach einem VBA-Makro. Überleg Dir mal, ob Du Dich damit anfreunden könntest. Erstellen und a bissl dokumentieren zum Verständnis sollte möglich sein.

Ich habe auch eine Beispieldatei, aber weiß leider nciht wie
ich diese anhänge.

Beispiel (Visualisierung) wär hilfreich.

Danke schon Mal im Voraus

Gern, wenn wir ein Ergebnis erarbeiten konnten.

#4

Hallo,

danke für deine netten Worte, wobei ich der Meinung bin, dass eine gewisse Umgangsform schon sein sollte.
Ich habe deinen Rat befolgt und den Link gleich mal probiert.

[URL=[http://www.file-upload.net/download-7289107/Test.xls…](http://www.file-upload.net/download-7289107/Test.xlsx.html]Test.xlsx[/URL)]

Hoffe das dieser Link geht und darin wird auch einiges erklärt.

Danke

#5

Hallo,
ich habe heute schon eine Beispieldatei (dank N.N.) hochgeladen in der mein Problem beschrieben ist.
Zu deinen Fragen :
Ein GANTT-Diagramm stellt zeitliche Abfolgen in grafischen Balken dar
Der Name in dem benannten Bereich ist der Mitarbeitername, dieser ist aber immer mit einer Zahl verbunden.

Die Urlaubstage werden fortlaufend (wie im Beispiel) dargestellt und es können auch ganze Wochen sein, wie halt der Urlaub so ist:smile:

Es sollen die Tage pro Monat aufgeteilt werden (auch wieder im Beispiel) z.B. 28.06 – 05.07 sind 3 Tage Juni und 5 Tage Juli.

Es gibt eine Liste in der jeder Mitarbeiter aufgeführt ist und hier sollen die Urlaubstage den Monaten zugeordnet werden.

Gegen ein Makro würde im Allgemeinen nichts sprechen, leider ist bei uns diese Funktion deaktiviert (könnte man aber wohl auch aktivieren wenn es nicht anders geht). Leider kenne ich mich mit der Makrowelt nicht wirklich aus, aber vielleicht hast du hier ja was Passendes.

Danke und schönen Tag noch

#6

Hallo Tom28A,

wobei ich der Meinung bin, dass eine gewisse Umgangsform schon sein sollte.

da bin ich ganz deiner Meinung. Aber wenn du mal etwas länger dabei bist und auf andere Beiträge von neu angemeldeten Usern schaust, dann wirst du sehen dass du da leider die Ausnahme bist.

Ich habe deinen Rat befolgt und den Link gleich mal probiert.

Leider ist das nicht der richtige Link. Dieser Link führt zur Seite wo du die Datei hochladen kannst. Wenn du deine Datei hochgeladen hast, dann wirst du ja auf eine weitere Seite geleitet. Dort musst du dann den richtigen Link kopieren.

Siehe hier die Erklärung dazu: http://www.abload.de/img/1qvll7.jpg

Hoffe das dieser Link geht und darin wird auch einiges erklärt.

Wie gesagt, war das leider der falsche Link. Lade die Datei noch mal hoch, und verwende dann den Link den ich in meiner Erklärung gezeigt habe.

Gruß (und noch mal viel Erfolg)
N.N

#7

Hallo,

auf ein neues.

http://www.file-upload.net/download-7289308/Test.xls…

Wer Lesen kann ist klar im Vorteil :smile:

Sorry

#8

Hallo Tom28A,

auf ein neues.

http://www.file-upload.net/download-7289308/Test.xls…

ich wollte dir nur die Rückmeldung geben dass der Link diesmal funktioniert. Wie ich aber schon bei meiner ersten Antwort geschrieben habe, werde ich dir bei der Lösung deines eigentlichen Problems nicht helfen können.

Ich wünsche dir viel Glück dass dir einer der Experten hier helfen kann.

Gruß
N.N

#9

Hallo Tom28A,

vielen Dank für den Upload. Der bringt etwas mehr Licht in die Sache.
Momentan sehe ich noch keinen Lösungsansatz, dieses Problem ausschließlich mit Formeln zu lösen. Da gibt es aber sicher erfahrenere Excelianer als mich.

Folgen die verschiedenen Mitarbeiter auf Sheet 1 untereinander? oder nebeneinander? Woher weiss die Spalte E, welches Samstage, Sonn- und Feiertage sind? oder wird das manuell eingetragen?

Auf Sheet 2 in Spalte S stehen für mich unverständlich auch halbe Tage, obwohl nach links nur ganze Tage eingetragen wurden (Bug?).

Ich bin mir relativ sicher, dass ich eine VBA-Lösung anbieten könnte, wenn Formeln nicht helfen. Das könnte ich, hoffe ich, auch so weit kommentieren, dass es weitergepflegt werden kann.
Sollten die Feiertage mit abgefragt werden, wär wichtig, um welches Bundesland es sich handelt.

Wenn VBA in Frage kommt, lass es mich wissen.

MfG MwieMichel

#10

hallo,
Das alles in eine Formel zu packen halte ich für schwirig.

ich würde das mit Hilfsspalten lösen und zuerst für jedes einzelne Datum im Jahr prüfen ob es ein Urlaubstag ist oder nicht.
Hier kann, man dann zB. die Wochenenden sofort raus nehmen.
DITO später die Feiertage.

Das Zählen, wie viele Tage dann in den relevanten Mo…

Gruß Holger

#11

Hallo MwieMichel,
wenn du mir hier ein VBA erstellen kannst, dann könnte ich das (wie du schon angemerkt hast) nur bei mir laufen lassen, darauf bin ich selber noch nicht so wirklich gekommen:smile:.

Auf Sheet 1 stehen die Mitarbeiter immer untereinander und um ein Filtern zu realisieren, steht der Name im grauen Feld farbig und in den weisen Feldern darunter in weis.

Für die Feiertage inklusive der Wochenenden habe ich folgende Formel „=WENN(D20="";" ";NETTOARBEITSTAGE.INTL(D20;F20;Feiertage!$A$32;Feiertage!$A$2:blush:A$23))“ hier verweise ich auf eine Liste mit den Feiertagen und für das Wochenende gibt es einen Excel-Code ('0000011).
Somit ist das Ergebnis in Spalte E schon das richtige.

Ich habe auch eine Formel gefunden die mir die Tage in die entsprechende Monate teilen würde, jedoch weiß ich dann nicht weiter bzw. wie ich das Ergebnis in die Tabelle auf Sheet2 bringe. „=WENN(I36>DATUM(JAHR(G36);MONAT(G36)+1;0);NETTOARBEITSTAGE.INTL(G36;(DATUM(JAHR(G36);MONAT(G36)+1;0));Feiertage!$A$32;Feiertage!$A$2:blush:A$23);NETTOARBEITSTAGE.INTL(G36;I36;Feiertage!$A$32;Feiertage!$A$2:blush:A$23))&" Urlaubstage in Monat "&MONAT(G36)“.

Die halben Tage auf Sheet2 sind außer Acht zu lassen.

Jetzt kommt noch eine Schwierigkeit hinzu:smile:, wir haben fast in jedem Bundesland jemanden sitzen und daher müsste dann das Makro den Namen bzw. seine zugehörige Zahl dem Bundesland zuordnen.
Hoffe du kannst mir helfen

Danke für deine Bemühungen

#12

Hallo
hasst du dir meine Formellösung /t/urlaubsplanung-mit-aufteilung-der-tage-in-monate/…

Ich habe als Beispiel dort die Wochenenden raus gerechnet.
Wenn die Feiertage des bestimmten Bundeslandes irgendwo stehen oder berechnet werden,
geht das genau so auch da mit

Gruß Holger

#13

Hallo MwieMichel,

Hallo Tom28A,

wenn du mir hier ein VBA erstellen kannst, dann könnte ich das
(wie du schon angemerkt hast) nur bei mir laufen lassen,
darauf bin ich selber noch nicht so wirklich gekommen:smile:.

Wenn ich kann, würde dich gerne helfen.
Wichtig wäre mir, dass Du die Schritte des Makros selbst nachvollziehen kannst, für den Fall, dass Änderungen erforderlich werden. Und meistens entwickeln sich die Dinger mit der Zeit…

Auf Sheet 1 stehen die Mitarbeiter immer untereinander und um
ein Filtern zu realisieren, steht der Name im grauen Feld
farbig und in den weisen Feldern darunter in weis.

Mit der Nummer und / oder dem Mitarbeiternamen (der hat doch bestimmt auch einen Nachnamen?) ließe sich evtl. die Zuordnung zu einem Bundesland erledigen. Verschiebt sich ggf. die Arbeit eines Mitarbeiters von einem in ein anderes Bundesland? womöglich auch noch unter dem Jahr?

Für die Feiertage inklusive der Wochenenden habe ich folgende
Formel „=WENN(D20="";"
";NETTOARBEITSTAGE.INTL(D20;F20;Feiertage!$A$32;Feiertage!$A$2:
$A$23))“ hier verweise ich auf eine Liste mit den Feiertagen
und für das Wochenende gibt es einen Excel-Code ('0000011).
Somit ist das Ergebnis in Spalte E schon das richtige.

Ich bin beeindruckt. Mit VBA würde ich vermutlich die Tage von Anfang bis Ende zählen (wenn nicht Samstag und nicht Sonntag, und nicht Feiertag) (die Feiertage würde ich einer Liste, je nach Bundesland entnehmen; könnte der besseren Nachvollziehbarkeit auf einem eigenen Tabellenblatteingetragen werden).

Ich habe auch eine Formel gefunden die mir die Tage in die
entsprechende Monate teilen würde, jedoch weiß ich dann nicht
weiter bzw. wie ich das Ergebnis in die Tabelle auf Sheet2
bringe.
„=WENN(I36>DATUM(JAHR(G36);MONAT(G36)+1;0);NETTOARBEITSTAGE.INT
L(G36;(DATUM(JAHR(G36);MONAT(G36)+1;0));Feiertage!$A$32;Feierta
ge!$A$2:blush:A$23);NETTOARBEITSTAGE.INTL(G36;I36;Feiertage!$A$32;Fe
iertage!$A$2:blush:A$23))&" Urlaubstage in Monat "&MONAT(G36)“.

Mit VBA würde ich die Tage (wenn nicht Sams- und nicht Sonn- und nicht Feiertag) auf den Monat prüfen und zählen.

Die halben Tage auf Sheet2 sind außer Acht zu lassen.

Jetzt kommt noch eine Schwierigkeit hinzu:smile:, wir haben fast
in jedem Bundesland jemanden sitzen und daher müsste dann das
Makro den Namen bzw. seine zugehörige Zahl dem Bundesland
zuordnen.

Das liesse sich über Mitarbeiternamen oder Zahl (?) erledigen. Schwierig würde es, wenn dies sich im Laufe eines Jahres ändern würde.

Hoffe du kannst mir helfen

Danke für deine Bemühungen

Würd mich freuen, wenn wir das Ding zum laufen bringen.
MfG MwieMichel

#14

Hallo,

hab jetzt die Formellösung gesehen und kann mich aber erst leider nächste Woche damit beschäftigen.
Was mir aber noch nicht klar ist, wie du das mit den Feiertagen lösen willst?

Ich probier deine Formle jetzt dann mal aus und gebe dir dann nächste Woche Bescheid.

Danke schon mal

#15

Hallo
ich habe dir mal zum zeigen ein Tabellenblatt „Feiertage“ von mir angehängt
und verknüpft.
Hier kannst du für jedes Bundesland die relevanten Feiertage einstellen.

Wählst du jetzt in Sheet1!H5 das Bundesland, werden die relevanten Tage nicht mehr berücksichtigt

Schau mal

Gruß Holger

#16

Hallo,

ich habe deine Formeln ausprobiert und in meinem GANTT-Diagramm funktioniert auch alles so weit.
Ich habe aber leider ein Problem mit deinen Namen definiert Formeln. Wenn ich diese Formeln auf meine Anwendung anpasse dann kommt immer der NV Wert. Egal wie ich auch die Zellen formatiere und was ich auch mache, es kommt immer dasselbe.
Vielleicht verstehe ich die Funktion hier nicht ganz, aber da bin ich mit meinem Latein am Ende.
Könntest du mir das nochmal erläutern?

Des Weiteren funktioniert das mit den Bundesländer soweit auch, aber bei mir ist das Problem, dass die verschiedensten Mitarbeiter aus den unterschiedlichsten Bundesländern kommen und daher müsste ich das immer auf die Person beziehen, hast du hierzu noch eine Idee?

Danke für deine Hilfe, wenn man hier Sterne verteilen kann, dann bekommst du das Maximum :smile:

Danke

P.s ich könnte dir auch meine Datei schicken, aber ich möchte diese nicht ins Netz stellen!

#17

Hallo
es ist natürlich schwirig den Fehler ohne konkrete Tabelle zu finden.
Die Namen habe ich nur vergeben damit man die Funktion der Formeln besser erkennen kann.
Egal ob Name oder direkter Bezug, ganz wichtig ist das alle Spalten / Zeilen gleich groß sind.

Des Weiteren funktioniert das mit den Bundesländer soweit auch, aber bei mir ist das Problem, dass die verschiedensten Mitarbeiter aus den unterschiedlichsten Bundesländern kommen und daher müsste ich das immer auf die Person beziehen, hast du hierzu noch eine Idee?

Die Prüfung vom Feiertag steht global in in Zeile 6
Die Formeln in den Zeilen der Kollegen beziehen sich auf das WAHR/FALSCH

Wenn die Prüfung statt dessen mit in den Zeilen der Kollegen steht,
dann kann man für jeden Kollegen auch das Bundesland eingeben.

P.s ich könnte dir auch meine Datei schicken, aber ich möchte diese nicht ins Netz stellen!

OK, ich sende dir mal eine PN
Gruß Holger