Excel dynamischer Jahrsakalender?
Hallo, ich bräuchte in Excel einen dynamischen Jahreskalender.
Habe leider keine passenden Vorlagen gefunden. Das mit dem selbst erstellen hat auch nicht so funktioniert.
Hätte da jemand eine Anleitung oder eine Vorlage für mich?
In Excel ist unter Vorlagen ein Jahreskalender. Dieser entspricht aber nicht ganz dem Aufbau, den ich gerne hätte.
Im Anhang ein Bild, wie der Kalender ungefähr aussehen sollte.
Der Kalender soll, wenn ich die Jahreszahl ändere, automatisch die Tage und die Feiertage anpassen.
Ich hoffe, jemand kann mir hier weiterhelfen.
Danke im voraus.
Gruß
Burak!
3 Antworten
Etwas Bastelarbeit hast Du damit schon, es reichen aber wenige Formeln und bedingte Formatierungen aus, das meiste sind normale Zellformatierungen.
Annahme: In A1 steht das Jahr
Spalte A ist der jeweilige Tag des Montag
für den jeweils Monatsersten =datum(a1;1;1) Das fettgedruckte ist der Monat, der muss für jede Monatsspalte natürlich angepasst werden.
Zellformatierung benutzerdefiniert: TT
Ab dem 2. Tag eines jeden Monats: Wenn der erste Tag für Januar bspw. in A3 steht, dann wäre A4 einfach =A3+1
Sonderfall Februar: Für den 29. stattdessen
=WENN(MONAT(E30+1)=MONAT(E30);E30+1;"")
-> E30 ist bei mir die Zeile mit dem 28.02
Spalte B ist, wenn man es genau machen will wie im Bild, nur der Wochentag. Das geht mit der Zellformatierung. Benutzerdefiniert und dort: TTT
Der Inhalt ist einfach =A3, d.h. den Wert von Spalte A übernehmen.
Spalte C mit Kalenderwoche: ="KW "&KALENDERWOCHE(A3;21)
Zu den Formatierungen:
Spalte B und C bekommen zunächst die Standardfarbe für Wochentage. Die Bereiche mit den Inhalten auch für die anderen Monate markieren. Dann neue Regel für bedingte Formatierung und dort die Formel:
=WOCHENTAG(A3;2)>5
In gleicher Weise geht das auch für die leeren Zellen in Spalte D.
Keine fertige Lösung kann ich Dir für die Feiertage geben, da ich nicht weiß, in welcher Form diese vorliegen. Wenn es einfach eine Liste mit Datumswerten ist, dann kann man eine weitere Regel für die bedingte Formatierung verwenden.
=ZÄHLENWENN(H:H;A3)>0
Spalte H wäre hier die Liste der Feiertage
@ Ninombre: Diesmal hab ich gottseidank erst mal aktualisiert, bevor ich mir die Mühe einer Beschreibung gemacht hab, erstellt hab ichs schon, wenn auch mit anderen Formeln als Du!
Vielleicht stell ichs doch noch ein, denn die Unterschiede könnten durchaus interessant sein! (aber erst später, ich bewerte dann Deine AW, wenn ichs hab!)
Soweit ich gelesen habe, soll es mit 21 die exakte KW ausrechnen. Dies entspricht der in ISO 8601 spezifischen Methode, welche im allgemeinen als europäisches Wochennummerierungssystem bezeichnet wird.
Dass ich die KW ab Sonntag oder Montag berechnen kann, das weiß ich.
Jedoch möchte ich, dass mir die KW NUR hinter Montag angezeigt wird und nicht hinter allen Tagen. Ich könnte natürlich nur die Zelle auswählen und die KW einfügen aber es soll ja, wenn ich ein anderes Jahr eintrage, automatisch die KW hinter den Montag schreiben,wenn dies möglich ist.
Gruß
Burak
Dann bette es in eine Wenn-Formel
=WENN(WOCHENTAG(A3;2)=1;"KW"&KALENDERWOCHE(A3;2);"")
Gruß aus Berlin
Perfekt! Danke dir.
Habe jedoch statt ;"KW"&KALENDERWOCHE(A3;2);"")
die 21 dahinter genommen.
Jetzt passt alles.
Der Vollständigkeithalber:
in XL2007 wird die Kalenderwoche korrekt (nach DIN EN 28601) mit:
=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)
(aus excelformeln.de)
Gruß aus Berlin
Gibt es in Excel eine Formel, in der jeder 1. Mittwoch zb. grün, jeder 2. gelb und jeder 3. grün markiert wird? Nachdem alle 3 durch sind sollte es von anfang losgehen. Oder dass die Kalenderwochen stattdessen automatisch in der jeweiligen Farbe angezeigt werden?
Gruß aus Frankfurt
Meintest Du das pro Spalte oder insgesamt? Für insgesamt, d.h. fortlaufend habe ich spontan noch keine Lösung.
Pro Spalte ginge es so, ist ist allerdings pro Farbe eine eigene Regel notwendig:
=UND(WOCHENTAG(A1;2)=3;REST(SUMMENPRODUKT((WOCHENTAG(A$1:A1;2)=3)*1);3)=1)
Annahme, dass das Datum in Spalte A steht. Auf die Art wird der 1. und 4. Mittwoch eines Monats erfasst. Für den 2. Mittwoch wäre die fett gedruckte 1 in eine 2 zu ändern. FÜr den 3. Mittwoch in eine 0
Am besten wäre es insgesamt gewesen. Trotzdem Danke für deine Bemühungen.
Habe jetzt die Liste mit den Feiertagen fertig. Habe mir die beweglichen Feiertage über den Ostersonntag berechnen lassen. Diese habe ich unter einem anderen Blatt namens Feiertage gespeichert.
Jedoch kriege ich sie nicht in den Kalender eingebunden.
Habe das Jahr unter Z2 - den 1. Januar unter A5 - Den Tag zu dem Datum unter B5 mit =A5
C5 hätte ich frei für die Feiertage (wenn dies an dem Tag zutrifft)
Unter D5 habe ich die KW
E5 ist auch frei.
Der Februar fängt dann mit F5 an.
Wie müsste ich jetzt vorgehen, damit er die Feiertage z.b unter C5 anzeigen soll?
Nochmals Vielen Dank an die, die mir bis jetzt schon sehr weit geholfen haben.
Gruß
Burak
Eine Möglichkeit wäre eine neue Regel für bedingte Formatierung:
=ZÄHLENWENN(Feiertage!B:B;A5) wenn das Datum des Feiertags in Spalte B auf dem neuen Tabellenblatt steht.
Den jeweiligen Namen des Feiertags anzuzeigen,ginge mit SVERWEIS - allerdings darf dann keiner was in die entsprechende Spalte schreiben, da sonst die Formel weg ist.
zum 1., 2., 3. Mittwoch durchlaufend (die 21 hat nichts mit obiger KW zu tun, sondern steht für 3-Wochen=21-Tage-Zyklus:
=Wenn(Rest(Datum;21)=4;"1.";"")&Wenn(Rest(Datum;21)=11;"2.";"")&
Wenn(Rest(Datum;21)=18;"3.";"")&"Mi"
Wobei sich das je nach Definition des 1. Mi auch zyklisch verschieben kann, aber als generelle Regel reichts.
Du kannst aber auch den zB 3.Mi im Monat erfassen, zB den Buß-und-Bettag im November:
=wenn(Und(Rest(Datum;7)=4;Monat(Datum)=11;Monat(Datum-21)<Monat(Datum);Monat(Datum+7)=Monat(Datum));"Buß-und-Bettag";""), sollte leer bleiben für alle anderen Tage des Jahres und sollte so an jede andere Formel angehängt werden können -nur als Denkanregung für weitere Kalenderformeln. (im Moment Trockenübung, aber ich definiere so meine wiederkehrenden Termine, zB. 1. und 3. Di im Monat etc.)
Vielen Dank! Hab jetzt den Kalender fertig.
Jedoch hätte ich noch eine Frage:
Ist es möglich, Excel zu sagen, dass er hinter jeden Montag die KW dazu schreiben soll? bzw. in einer eigenen Zelle hinter Montag?
Gruß
Burak
Outlook oder Acess ist perfekt dafür , Excel nicht so
das war einmal. Das beste Programm ist immer das, das man beherrscht.
Wenn ich diese Programme nicht kann, ist es nur legitim, es in xl zu versuchen.
Und es geht, wie nicht nur die AW Ninombre zeigt, auch ich habe sowas in mehreren Ausfertigungen schpon erstellt.
A1:'2015 (vorsichtshalber als Text) , A2: Januar , A3: 1 , A4: 2
A3:A4 markieren, runterziehen bis A30 (=28),
in A31 (=29.) Formel: =WENNFEHLER(TAG(A30+1&"."&A$2&"."&$A$1);"")
Bendef Format TT. ru'ziehen bis A34 (A34 nur zum Test, muss blank ergeben)
B3: =WENNFEHLER((A3&"."&A$2&"."&$A$1)+0;""),
Bendef Format TTT, dann bis B34 runterziehen
C3: =WENNFEHLER(KALENDERWOCHE(WENN(REST(B3;7)=2;B3;"");21)&".KW";"") -runterziehen.
Bedingte Formatierung muss in einer Zeile Zellenweise erfolgen, wenn nach rechts kopiert werden soll, generell: Formel verwenden. :
D3: =Rest(B3;7)<2 ebenso A3, B2, C3. xl schlägt wahrscheinlich automatisch =Rest($B$3;... vor, die $-Zeichen löschen! (s.Anm.1)
Format Zeile3 übertragen auf Zeile4, dann A3:d4 runterziehen bis Zeile 30, vier Zeilen zwischendrin markieren, Format übertragen auf Zeilen31(=29.) :34(blank)
zusätzliche bedingte Formatierung für zB A3: Formel verwenden, Formel: A3=heute(), roter Rahmen, zB violettes Muster
Spaltenbreiten festlegen (A:D), Grundformatierung(Farbe) erstellen.
Format übertragen von A:D(am Spaltenkopf) nach E:H (ebenfalls), i:L, ... AS:AV
ggf. Jahreszahl über Februar löschen (s.Anm.2)
A3:D34 markieren nach rechts ziehen mit Kontextmenü "Zellen kopieren" bis AV
Anm.1: ich bevorzuge die Restformel, weil sie eindeutig (unabhängig vom Gebietsschema ist.
Anm.2: vllt ist es kürzer, gleich bis AV zu kopieren und hinterher die falschen Jahreszahlen zu löschen, Ermessenssache.
Anm. 3: Als Liste ist bei mir Januar, Februar...Dezember eingestellt, da ich sie nicht löschen kann, nehme ich an, dass es eine integrierte Liste ist. (Datei-Optionen-erweitert-kurz vor Ende
Ich hoffe, wenig Fehler reingebracht zu haben...
Bei mir (xl2007) kann ich die KALENDERWOCHE(A3;1) oder
KALENDERWOCHE(A3;2) mit unterschiedlichem Wochenanfang (So oder Mo) berechnen.
Was bitte ist 21 ? bei neueren XL-Versionen?
Gruß aus Berlin