Urlaubsplan verbessern und dynamisch machen Excel

Computer: Software & Programmierung Office Software
#1

So liebe Besucher und Experten,

ich habe mal wieder die ein oder andere Nuss zu knacken bei einem selbstgebastelten Urlaubsplan, der wie ich finde gar nicht mal so schlecht ist, aber sicherlich einige Verbesserungen und Funktionen dazu beitragen könnten, dass das Teil noch einfacher und intuitiver wird.

Darum habe ich es einfach mal hochgeladen zur Ansicht um zum Ausprobieren :smile: Natürlich auch zur freien Verwendung, falls jemand Interesse daran hat.

Klick here!

Es handelt sich übrigens um ein Excel 2007 Sheet!

Zu meinen Fragen, Ideen und Verbesserungsvorschlägen, bei denen ich leider nicht weiterkomme und Hilfe benötige:

  1. Ich würde gerne in den jeweiligen Monaten in Zelle G5 bis AK5 anzeigen lassen, dass es sich um einen Feiertag handelt. Dafür soll dort einfach das Kürzel „FT“ auftauchen. Die Feiertage sind schon in der Eingabetabelle hinterlegt und passen sich an das jeweilige Jahr an. Es sind nur Feiertage, die für uns wichtig sind.

  2. Die bedingte Formatierung in den Monaten soll folgendes machen: Wenn es sich um einen Feiertag handelt, also das Kürzel „FT“ auftaucht, soll die komplette „Spalte“ genauso vergraut angezeigt werden wie die Samstage und Sonntage.

  3. Ich würde gerne in den monatlichen Zeilen „2“ die KW (Kalenderwochen) anzeigen lassen, nur schaffe ich es irgendwie nicht, die Formel richtig einzubauen…

  4. In der Eingabemaske (Tabellenblatt „MA_Daten“) habe ich in Zelle E1 das Jahr 2011 als Datum 01.01.2011 eingebaut und kann daher auch weiter in den Monaten verwendet werden. ABER: Wieso kann ich die selbe Zelle nicht nehmen, um die Feiertage zu berechnen? Dahinter bin ich nicht gekommen. Die eingebauten Formeln für die Feiertage benötigen eine reine Zelle mit dem Inhalt „2011“, aber das muss doch auch einer Zelle im Datumsformat und dem Wert 01.01.2011 gehen, oder? Dann könnte ich mir nämlich Zelle D30 sparen und mich immer auf die eine Zelle E1 beziehen. Kann das jemand lösen?

Ansonsten bin ich für weitere Kritiken und Hinweise aufgeschlossen und danke schonmal im Voraus!

Grüße
CDJ

#2

Hallo Cosmic,

Klick here!

  1. Ich würde gerne in den jeweiligen Monaten in Zelle G5 bis
    AK5 anzeigen lassen, dass es sich um einen Feiertag handelt.
    Dafür soll dort einfach das Kürzel „FT“ auftauchen. Die
    Feiertage sind schon in der Eingabetabelle hinterlegt und
    passen sich an das jeweilige Jahr an. Es sind nur Feiertage,
    die für uns wichtig sind.

(Mit Eingabetabelle ist MA_Daten gemein, nur mal so)

Generell ist deine Feitertagstabelle falschrum, besser ist vorne das Dateum und rechts davon der name des Feiertags, wegen Sverweis usw.
Wer weiß schon ob du nicht in 2 Wochen haben willst daß nicht FT angezeigt wird sondern der Feiertagsname.

Vergib für die Liste mit den Feiertagsdatümern einen Namen.
Dann kannst du in der Bed. Formatierung mit Zählenwenn, Vergleich o.ä. prüfen ob das Datum in der Liste „namen“ steht, dementsprechend dann die Zelle färben lassen.

Schau mal hier bei w-w-w zwei Threads tiefer, da habe ich Formeln gebastelt, die im Gegensatz zu deinen für alle Monate automatisch gelten. Bei dir muß man ja manuell pro jahr und Monat Zellinhalte löschen.

  1. Ich würde gerne in den monatlichen Zeilen „2“ die KW
    (Kalenderwochen) anzeigen lassen, nur schaffe ich es irgendwie
    nicht, die Formel richtig einzubauen…

Welche Formel? Die Excelfunktion „Kalenderwoche“ rechnet ggfs. falsch.
Schau mal zu
http://www.excelformeln.de
klick da auf Formeln, dann im Suchformular Kalenderwoche eingeben…

  1. In der Eingabemaske (Tabellenblatt „MA_Daten“) habe ich in
    Zelle E1 das Jahr 2011 als Datum 01.01.2011 eingebaut und kann
    daher auch weiter in den Monaten verwendet werden. ABER: Wieso
    kann ich die selbe Zelle nicht nehmen, um die Feiertage zu
    berechnen? Dahinter bin ich nicht gekommen. Die eingebauten
    Formeln für die Feiertage benötigen eine reine Zelle mit dem
    Inhalt „2011“, aber das muss doch auch einer Zelle im
    Datumsformat und dem Wert 01.01.2011 gehen, oder? Dann könnte
    ich mir nämlich Zelle D30 sparen und mich immer auf die eine
    Zelle E1 beziehen. Kann das jemand lösen?

Schau in dem angegebenen Link auch mal zu Feiertag…
Und ja, prinzipiell ist es egal ob man mit E1 oder D30 weiterrechnet, in beiden Fällen muß man damit Feiertage oder sonstwas berechnen/filtern.
Muß jetzt weg, schau ich mir nachher an.

Gruß
Reinhard

#3

(Mit Eingabetabelle ist MA_Daten gemein, nur mal so)

Das stimmt, ist Absicht :wink:

Generell ist deine Feitertagstabelle falschrum, besser ist
vorne das Dateum und rechts davon der name des Feiertags,
wegen Sverweis usw.

Habe ich nun umgedreht, Datum links, Feiertagsname rechts.

Wer weiß schon ob du nicht in 2 Wochen haben willst daß nicht
FT angezeigt wird sondern der Feiertagsname.

Kann passieren, muss aber nicht…

Vergib für die Liste mit den Feiertagsdatümern einen Namen.

Okay, und wie mache ich das??

Dann kannst du in der Bed. Formatierung mit Zählenwenn,
Vergleich o.ä. prüfen ob das Datum in der Liste „namen“ steht,
dementsprechend dann die Zelle färben lassen.

Ja, es soll ja nicht nur die Zelle einfärben, sondern die gesamte Spalte, so wie eben bei Sams- und Sonntag. Wie realisiere ich das, nachdem ich die Liste bearbeitet habe und auch das Kürzel „FT“ dort steht wo es soll?

Schau mal hier bei w-w-w zwei Threads tiefer, da habe ich
Formeln gebastelt, die im Gegensatz zu deinen für alle Monate
automatisch gelten. Bei dir muß man ja manuell pro jahr und
Monat Zellinhalte löschen.

Ähm, also löschen muss man höchstens die eingetragenen Urlaubstage, was ja halb so wild ist. Mich stört es nicht zum Anfang des Jahres einfach alles herauszunehmen und den Resturlaub auf das neue Jahr zu übertragen. Zudem muss ich ja nur das Datum auf MA_Daten auf 2012, 2013 usw. stellen und dann ändert es die Monate automatisch über das gesamte Blatt, von dem her kein Problem, ein bisschen Arbeit darfs noch machen :smile: Zudem sperre ich sowieso alle Bereiche und lasse nur auf MA_Daten die Eingabe zu und eben in den Monaten die Eintragungen.

Welche Formel? Die Excelfunktion „Kalenderwoche“ rechnet ggfs.
falsch.
Schau mal zu
http://www.excelformeln.de
klick da auf Formeln, dann im Suchformular Kalenderwoche
eingeben…

Ja, habe ich schon gesehen, nur wo baue ich das genau ein und vor allem, auf welche Zelle beziehe ich mich mit dem Anfangsdatum? Ich habe ja immer noch das Problem bei diversen Zellen (Feiertage etc.), dass „2011“ akzeptiert wird, 01.01.2011 aber nicht *huh*?

Schau in dem angegebenen Link auch mal zu Feiertag…
Und ja, prinzipiell ist es egal ob man mit E1 oder D30
weiterrechnet, in beiden Fällen muß man damit Feiertage oder
sonstwas berechnen/filtern.

Ja, „muss“ bzw. „müsste“, aber tuts ja nicht. Kannst gerne mal ausprobieren und das als Datum eintragen und so stehenlassen, dass nur 2011 erscheint (also Formatierung „JJJJ“), dann wird alles falsch berechnet oder springt auf 1905 um (warum auch immer…).

Ich wäre für jede Hilfe wirklich sehr dankbar und bin es jetzt schon, brauche nur noch den kleinen Kick und „Aha-Effekt“ damits klappt.

Danke schonmal jetzt für die Infos, aber ich kann wohl erst wieder dran basteln, wenn das hier geklärt ist.

Grüße
CDJ

#4

Noch was ist mir eingefallen:

  1. Ist es möglich, dass das Problem mit dem Datum (01.01.2011) ausschlaggebend ist und dadurch vielleicht irgendwas nicht funktioniert?

  2. Ich habe nun der Tabelle mit den Feiertagen einen Namen gegeben, habe es gefunden (links oben Namen eingeben statt Zelle, natürlich mit Markierung der Tabelle).

Nun bräuchte ich ja eigentlich nur in der Zelle in der „FT“ erscheinen soll eine Fomel, die ungefähr so lautet:

WENN ZELLE G3 „irgendeinem Wert“ aus Bereich „Feiertage“ entspricht, dann schreibe „FT“ oder lass leer. Das muss ich dann nur auf die weiteren Zellen kopieren (natürlich den Bezug mit „$“, ist ja eh klar) und genau diese Formel fällt mir nicht ein.

Ich habe es schon sau kompliziert probiert mit
WENN Zelle G3 A32 ODER A33 ODER A34 etc. entspricht, dann…

Aber auch das hat nicht geklappt.

Himmelherrgott, es muss doch eine Möglichkeit geben das irgendwie zu schaffen, kann doch nicht so schwer sein.

Ich möchte halt nicht mit Makros etc. arbeiten, die Tabelle soll rein mit Excelbefehlen arbeiten (mit Markos, VB Script etc. würde ich mich eh nicht auskennen :wink:.

Biiiiiiitteeeeee, sagt mir jetzt irgendwer, wie man sowas macht? Einfach einen Spaltenbereich mit einer Zelle vergleichen und dann eine Aktion machen, das kann doch nicht sooo schwer sein, oder?

Grüße CDJ

#5

Hallo Cosmic,

(Mit Eingabetabelle ist MA_Daten gemein, nur mal so)

Das stimmt, ist Absicht :wink:

Absicht? Mein Hinweis war an andere gerichtet damit sie deine Liste leichter finden und nicht rumirren müssen.

Generell ist deine Feitertagstabelle falschrum, besser ist
vorne das Dateum und rechts davon der name des Feiertags,
wegen Sverweis usw.

Habe ich nun umgedreht, Datum links, Feiertagsname rechts.

Gut.

Wer weiß schon ob du nicht in 2 Wochen haben willst daß nicht
FT angezeigt wird sondern der Feiertagsname.

Kann passieren, muss aber nicht…

Wenn es nicht passiert isses egal. Passiert es ist deine Tabelle vorbereitet, darum ging/geht es mir.

Vergib für die Liste mit den Feiertagsdatümern einen Namen.

Okay, und wie mache ich das??

Bei XL2007 Formeln—Namen definieren…

Dann kannst du in der Bed. Formatierung mit Zählenwenn,
Vergleich o.ä. prüfen ob das Datum in der Liste „namen“ steht,
dementsprechend dann die Zelle färben lassen.

Ja, es soll ja nicht nur die Zelle einfärben, sondern die
gesamte Spalte, so wie eben bei Sams- und Sonntag. Wie
realisiere ich das, nachdem ich die Liste bearbeitet habe und
auch das Kürzel „FT“ dort steht wo es soll?

Du hast ja schon bed. Formatierungen eingesetzt. Einige Formeln dabei sind mir unklar wie
=(G1950)=2
?

Wochentag() hat zwei Parameter, im zweiten kannst du festlegen mit welchem tag die Woche beginnt, wähle da Mo, ich glaub dann ist der parameter 2
dann so bei der bed. Formatierung:

=Wochentag($G$3;2)>5

Nun kannst du schon prüfen ob da irgendwo FT steht, warum?

Lösche all deine bed. Formatierungen und nehme nur eine bed. Formatierungsregel/formel:

=Oder(Wochentag($G$3;2)>5;Zählenwenn(Feiertagsbereich;$G$3)>0)

Oder teil das in zwei Formeln auf wenn du Feiertage andersfarbig haben willst.

Schau mal hier bei w-w-w zwei Threads tiefer, da habe ich
Formeln gebastelt, die im Gegensatz zu deinen für alle Monate
automatisch gelten. Bei dir muß man ja manuell pro jahr und
Monat Zellinhalte löschen.

Ähm, also löschen muss man höchstens die eingetragenen
Urlaubstage, was ja halb so wild ist.

Du irrst, du hast im Blatt Februar den 1.3., gehört der ins blatt Februar?

ein bisschen Arbeit darfs noch
machen :smile:

Kein Akt, drucks aus, arbeite mit Tipp-Ex , scanns wieder ein und nehme es als Hintergrundbild in Excel *grins*

Zudem sperre ich sowieso alle Bereiche und lasse nur
auf MA_Daten die Eingabe zu und eben in den Monaten die
Eintragungen.

Welche Eintragungen? ich sehe da nur zweimal eine 1 eingetragen, nicht ersi9chtlich was das bedeutet.

Welche Formel? Die Excelfunktion „Kalenderwoche“ rechnet ggfs.
falsch.
Schau mal zu
http://www.excelformeln.de
klick da auf Formeln, dann im Suchformular Kalenderwoche
eingeben…

Ja, habe ich schon gesehen, nur wo baue ich das genau ein und
vor allem, auf welche Zelle beziehe ich mich mit dem
Anfangsdatum? Ich habe ja immer noch das Problem bei diversen
Zellen (Feiertage etc.), dass „2011“ akzeptiert wird,
01.01.2011 aber nicht *huh*?

Das habe ich von Anfang an nicht verstanden. Du hast pro Blatt in z.B. G3 eine datum stehen.
dann schreibst du in eine Zelle
=kalenderwoche(G3)
Die 6 wird angezeigt. Da kalenderwoche falschanzeigt nimmste logischerweise die korrekte Formel, mir unklar wodran du da hängst und es nicht klappt.

Schau in dem angegebenen Link auch mal zu Feiertag…
Und ja, prinzipiell ist es egal ob man mit E1 oder D30
weiterrechnet, in beiden Fällen muß man damit Feiertage oder
sonstwas berechnen/filtern.

Ja, „muss“ bzw. „müsste“, aber tuts ja nicht. Kannst gerne mal
ausprobieren und das als Datum eintragen und so stehenlassen,
dass nur 2011 erscheint (also Formatierung „JJJJ“), dann wird
alles falsch berechnet oder springt auf 1905 um (warum auch
immer…).

Kann ich nicht nachvollziehen. Ändere ggfs. mal wie von mir geraten deine beispielmappe, ergänze sie mal um aussagekräftige Datenzeilwen damit man sieht was du da eigentlich eingetragen hast.

Gruß
Reinhard

#6

Hallo Cosmic,

  1. Ist es möglich, dass das Problem mit dem Datum (01.01.2011)
    ausschlaggebend ist und dadurch vielleicht irgendwas nicht
    funktioniert?

Prinzipiell nein, aus einem datumswert kannst du ermitteln ob er in der Feiertagsliste auftaucht. Wenn da was falsch läuft ist die Ermittlung marode.

Nun bräuchte ich ja eigentlich nur in der Zelle in der „FT“
erscheinen soll eine Fomel, die ungefähr so lautet:

Schreib in G2:
=WENN(ZÄHLENWENN(MA_Daten!$D$31:blush:D$49;G3)=0;"";„FT“)
und kopiere es nach rechts.

Gruß
Reinhard

#7

Absicht? Mein Hinweis war an andere gerichtet damit sie deine
Liste leichter finden und nicht rumirren müssen.

Ich habe statt „gemeint“ „gemein“ gelesen und dachte, Du beschreibst die Kompliziertheit mit „MA_“ am Anfang :wink:

Habe ich nun umgedreht, Datum links, Feiertagsname rechts.

Gut.

Ja, aber soll ich nun der gesamten Tabelle, also Daten und Namen, einen Namen geben, oder nur der Spalte mit den Daten?

Du hast ja schon bed. Formatierungen eingesetzt. Einige
Formeln dabei sind mir unklar wie
=(G1950)=2
?

Das ist eine gute Frage. Ich habe diese Beschreibung aus einem Forum, aber glaube nicht diesem hier…

Wochentag() hat zwei Parameter, im zweiten kannst du festlegen
mit welchem tag die Woche beginnt, wähle da Mo, ich glaub dann
ist der parameter 2
dann so bei der bed. Formatierung:

=Wochentag($G$3;2)>5

Nun kannst du schon prüfen ob da irgendwo FT steht, warum?

Lösche all deine bed. Formatierungen und nehme nur eine bed.
Formatierungsregel/formel:

=Oder(Wochentag($G$3;2)>5;Zählenwenn(Feiertagsbereich;$G$3)>0)

Oder teil das in zwei Formeln auf wenn du Feiertage
andersfarbig haben willst.

Puh… da steig ich langsam ehrlich nicht mehr durch, sorry. Das ist bestimmt eine gute Lösung, aber ich kann sie grad nicht nachvollziehen, weil ich nicht weiß, in welche Zelle ich was eintragen soll und welche bedingten Formatierungen ich wegmachen soll und neu eintragen soll und vor allem, wie ich diese übertragen kann auf alle anderen Zellen und vor allem Spalten. Wie wäre es mit einem Beispiel in meiner Liste als Rücksendung? Könnte meine Emailadresse posten, wäre für mich kein Problem. Oder anders?

Ähm, also löschen muss man höchstens die eingetragenen
Urlaubstage, was ja halb so wild ist.

Du irrst, du hast im Blatt Februar den 1.3., gehört der ins
blatt Februar?

Oh… komisch, beim ersten Blick hat das gestimmt, aber jetzt habe ich irgendwas gemacht und es stimmt. Puh, da nochmal überall was anderes eintragen wäre heftig, aber natürlich machbar. Wie sollte ich denn den ersten Tag des Monats betiteln? Reicht dann in der nächsten Spalte immer wieder „+1“ und soll ich in den Tagen unten auch was ändern "Mo, Di, Mi etc.) oder so stehenlassen?

Kein Akt, drucks aus, arbeite mit Tipp-Ex , scanns wieder ein
und nehme es als Hintergrundbild in Excel *grins*

Ja klar, dass das Teil nicht perfekt ist weiß ich, aber herunterladbare Urlaubsplaner sind entweder gesperrt und man kann sich nix abgucken, oder sind überhaupt nicht das, was man sich vorstellt. Drum ja komplett neu…

Zudem sperre ich sowieso alle Bereiche und lasse nur
auf MA_Daten die Eingabe zu und eben in den Monaten die
Eintragungen.

Welche Eintragungen? ich sehe da nur zweimal eine 1
eingetragen, nicht ersi9chtlich was das bedeutet.

Na, ich schicke doch keine Liste rum mit Namen und Daten von Mitarbeitern, darum ist nur ein Testname drin.

Das habe ich von Anfang an nicht verstanden. Du hast pro Blatt
in z.B. G3 eine datum stehen.
dann schreibst du in eine Zelle
=kalenderwoche(G3)
Die 6 wird angezeigt. Da kalenderwoche falschanzeigt nimmste
logischerweise die korrekte Formel, mir unklar wodran du da
hängst und es nicht klappt.

Ist einerseits gar nicht so wichtig, wäre nur praktisch, und ich kümmere mich da noch drum, ich muss mir nur die „richtige“ Formel genauer ansehen. Zudem wäre es praktisch, wenn immer zum jeweiligen Montag drüber die KW stehen würde und sonst nicht mehr, aber das ist wohl auch so eine Sache, die sicher total einfach ist, aber ich nur zu blöd bin…

Kann ich nicht nachvollziehen. Ändere ggfs. mal wie von mir
geraten deine beispielmappe, ergänze sie mal um
aussagekräftige Datenzeilwen damit man sieht was du da
eigentlich eingetragen hast.

Ist geändert und hier zu finden:
Klick hier!

Merci!
CDJ

#8

Hallo Cosmic,

Ja, aber soll ich nun der gesamten Tabelle, also Daten und
Namen, einen Namen geben, oder nur der Spalte mit den Daten?

vergib zwei Namen. Für nur „FT“ reicht die reine Datümerspalte.

Du hast ja schon bed. Formatierungen eingesetzt. Einige
Formeln dabei sind mir unklar wie
=(G1950)=2
?

Das ist eine gute Frage. Ich habe diese Beschreibung aus einem
Forum, aber glaube nicht diesem hier…

Da du in G1950 nichts stehen hast ist es egal geworden was mal in g1950 stand usw., löschen.

Nun kannst du schon prüfen ob da irgendwo FT steht, warum?

Nochmals, warum willst du auf „FT“ prüfen? FT erkennt man doch an der Färbung der Spalte.

=Oder(Wochentag($G$3;2)>5;Zählenwenn(Feiertagsbereich;$G$3)>0)

Puh… da steig ich langsam ehrlich nicht mehr durch, sorry.

Was ist schwierig vier Regeln zu löschen und nur noch eine reinzuschreiben?

und welche bedingten Formatierungen ich wegmachen soll

alle

und neu eintragen soll

nur die eine von eben.

und vor allem, wie ich
diese übertragen kann auf alle anderen Zellen und vor allem
Spalten.

Du markierst voeher den Zellenbereich für den diese bed. Formatierung geklten soll, dann trägst du die Bedingsformel ein, beziehst dich in ihr auf die linke obere Ecke des markierten Bereichs…
Da sich z.B bei der Wochenendsfärbung alle unteren Zellformatierungen auf den Datumswert in G3 beziehen mußt du da ein Dollar vor die 3 setzen.

Wie wäre es mit einem Beispiel in meiner Liste als
Rücksendung?

http://www.file-upload.net/download-3344696/Urlaubsp…

(Ist jetzt xlsm da Datei.Zuordnen als Makro zählt)

Na, ich schicke doch keine Liste rum mit Namen und Daten von
Mitarbeitern, darum ist nur ein Testname drin.

Naja, so 10-20 Testnamen sind besser mit unterschiedlichen Urlaubstagen usw.

Gruß
Reinhard

#9

Der Mann hat was drauf!!! :smile: Dankeeee!!!

#10

(Ist jetzt xlsm da Datei.Zuordnen als Makro zählt)

Wenn ich jetzt noch zurückverfolgen könnte, wo welches Makro hinterlegt ist, damit ich verstehe wie das funktioniert, wäre das klasse! Ich finde unter Makro nämlich nichts… und, ich denke mal, ich muss dann mein Excel Sheet genauso als xlsm speichern, oder?

Das war’s dann :smile: Vielen Dank!

#11

(Ist jetzt xlsm da Datei.Zuordnen als Makro zählt)

Wenn ich jetzt noch zurückverfolgen könnte, wo welches Makro
hinterlegt ist, damit ich verstehe wie das funktioniert, wäre
das klasse! Ich finde unter Makro nämlich nichts… und, ich
denke mal, ich muss dann mein Excel Sheet genauso als xlsm
speichern, oder?

Hallo Cosmic,

hättest du alles gemacht , auch die Beitragsfolge gelesen die ich dir sagte wüßtest du was ich mit Datei.Zuordnen meine.
Dieser Befehl ist ja korrektwerweise ein Makrobefehl aus Excel4, das hatte damals noch deutsche Befehlsnamen.

Aber ist Blödsinn nur deshalb Makros aktivieren zu müssen.
Ich schreib dir das um, dann bleibt es eine xlsx.

Gruß
Reinhard

#12

(Ist jetzt xlsm da Datei.Zuordnen als Makro zählt)

Wenn ich jetzt noch zurückverfolgen könnte, wo welches Makro
hinterlegt ist, damit ich verstehe wie das funktioniert, wäre
das klasse! Ich finde unter Makro nämlich nichts… und, ich
denke mal, ich muss dann mein Excel Sheet genauso als xlsm
speichern, oder?

Hallo Cosmic,

schreibe in A1 der Monatsblätter:

=DATWERT(„1.“&TEIL(ZELLE(„dateiname“;A1);FINDEN("]";ZELLE(„dateiname“;A1))+1;99)&"."&JAHR(Daten!$E$1))

und lösche den vergebenen Namen Nummer.
Dann müßte es wieder als xlsx abspeichbar sein.

Gruß
Reinhard