Zusammenfassung
Sie können ein dynamisches Kalenderraster in einem Excel-Arbeitsblatt mit einer Reihe von Formeln einrichten, wie in diesem Artikel erläutert. In dem gezeigten Beispiel lautet die Formel in B6:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Dabei ist "Start" der benannte Bereich K5 und enthält das Datum 1. September 2018.
Erläuterung
Hinweis: In diesem Beispiel wird davon ausgegangen, dass das Startdatum als erstes des Monats angegeben wird. Im Folgenden finden Sie eine Formel, die den ersten Tag des aktuellen Monats dynamisch zurückgibt.
Bei der gezeigten Anordnung des Rasters besteht das Hauptproblem darin, das Datum in der ersten Zelle des Kalenders zu berechnen (B6). Dies geschieht mit folgender Formel:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Diese Formel ermittelt den Sonntag vor dem ersten Tag des Monats, indem mit der Funktion AUSWÄHLEN die richtige Anzahl von Tagen auf den vorherigen Sonntag zurückgesetzt wird. CHOOSE funktioniert in dieser Situation perfekt, da für jeden Wochentag beliebige Werte zulässig sind. Wir verwenden diese Funktion, um null Tage zurückzusetzen, wenn der erste Tag des Monats ein Sonntag ist. Weitere Details zu diesem Problem finden Sie hier.
Wenn der erste Tag in B6 festgelegt ist, erhöhen die anderen Formeln im Raster einfach das vorherige Datum um eins, beginnend mit der Formel in C6:
=IF(B6"",B6,$H5)+1
Diese Formel testet die Zelle unmittelbar links auf einen Wert. Wenn kein Wert gefunden wird, wird ein Wert aus Spalte H in der obigen Zeile abgerufen. Hinweis $ H5 ist eine gemischte Referenz, um die Spalte zu sperren, während die Formel im gesamten Raster kopiert wird. Die gleiche Formel wird in allen Zellen außer B6 verwendet.
Bedingte Formatierungsregeln
Der Kalender verwendet bedingte Formatierungsformeln, die die Formatierung ändern, um frühere und zukünftige Monate zu schattieren und den aktuellen Tag hervorzuheben. Beide Regeln werden auf das gesamte Raster angewendet. Für frühere und nächste Monate lautet die Formel:
=MONTH(B6)MONTH(start)
Für den aktuellen Tag lautet die Formel:
=B6=TODAY()
Weitere Informationen finden Sie unter: Bedingte Formatierung mit Formeln (10 Beispiele)
Kalenderüberschrift
Der Kalendertitel - Monat und Jahr - wird mit dieser Formel in Zelle B4 berechnet:
=start
Formatiert mit dem benutzerdefinierten Zahlenformat "MMM JJJJ". Um den Titel über dem Kalender zu zentrieren, ist für den Bereich B4: H4 die horizontale Ausrichtung auf "Über Auswahl zentrieren" eingestellt. Dies ist eine bessere Option als das Zusammenführen von Zellen, da dadurch die Rasterstruktur im Arbeitsblatt nicht geändert wird.
Ewiger Kalender mit aktuellem Datum
Um einen Kalender zu erstellen, der basierend auf dem aktuellen Datum automatisch aktualisiert wird, können Sie in K5 die folgende Formel verwenden:
=EOMONTH(TODAY(),-1)+1
Diese Formel ermittelt das aktuelle Datum mit der Funktion TODAY und den ersten Tag des aktuellen Monats mit der Funktion EOMONTH. Ersetzen Sie HEUTE () durch ein bestimmtes Datum, um einen Kalender in einem anderen Monat zu erstellen. Weitere Details zur Funktionsweise von EOMONTH finden Sie hier.
Schritte zum Erstellen
- Gitterlinien ausblenden (optional)
- Fügen Sie B5 einen Rand hinzu: H11 (7R x 7C)
- Nennen Sie K5 "Start" und geben Sie ein Datum wie "1. September 2018" ein.
- Formel in B4 = Start
- Format B4 als "mmmm yyyy"
- Wählen Sie B4: H4 und stellen Sie die Ausrichtung auf "Über Auswahl zentrieren".
- Geben Sie im Bereich B5: H5 Tagesabkürzungen (SMTWTFS) ein.
- Formel in B6 = Start-WAHL (WOCHENTAG (Start), 0,1,2,3,4,5,6)
- Wählen Sie B6: H11 und wenden Sie das benutzerdefinierte Zahlenformat "d" an.
- Formel in C6 = IF (B6 "", B6, $ H5) +1
- Kopieren Sie die Formel in C6 in die verbleibenden Zellen im Kalenderraster
- Vorherige / Nächste bedingte Formatierungsregel hinzufügen (siehe Formel oben)
- Aktuelle bedingte Formatierungsregel hinzufügen (siehe Formel oben)
- Ändern Sie das zu testende Datum in K5 in ein anderes Datum für den ersten Monat
- Für den ewigen Kalender gilt die Formel in K5 = EOMONTH (TODAY (), - 1) +1
