So erstellen Sie einen sich dynamisch ändernden Kalender in Excel

In diesem Artikel zeigen wir Ihnen, wie Sie einen sich dynamisch ändernden Kalender in Excel erstellen.

Arbeitsmappe herunterladen

Variablen auffüllen

Der erste Schritt besteht darin, die Variablen zu erstellen, die in den Formeln verwendet werden sollen. Diese Variablen müssen die aktuellen Informationen bereitstellen, damit der Kalender automatisch aktualisiert werden kann. Hier ist die Liste der Variablen:

• Heutiges Datum
• Die Nummer des heutigen Tages des Monats
• Die Nummer des heutigen Monats
• Heutiges Jahr
• Das Datum des ersten Tages des heutigen Monats
• Die Nummer des heutigen Tages der Woche
• Die Nummer der aktuellen Woche des Jahres

Wir werden die Funktionen HEUTE, TAG, MONAT, JAHR, DATUM, WOCHENTAG und WOCHENNUMMER verwenden. Kurz gesagt, während HEUTE Funktion gibt das Datum des heutigen Tages, Tag, Monat, Jahr, Wochentag und WEEKNUM Funktionen analysieren die jeweiligen Datumswerte. Die Funktion DATE gibt den Datumswert des angegebenen Jahres, Monats und Tages zurück.

Im folgenden Screenshot sehen Sie die von uns angegebenen Namen und Formeln, die am 20.November 2020 generiert wurden.

Wir haben jede Variable benannt, um unsere Arbeit beim Erstellen von Formeln zu erleichtern. Der nächste Schritt zum Erstellen eines Kalenders in Excel ist das Erstellen einer Gliederung für die Kalenderoberfläche.

Gliederung für den Kalender in Excel

Bevor wir die tatsächlichen Formeln erstellen, die die Tage des Monats generieren, müssen wir zuerst eine Gliederung platzieren, um die Erstellung von Formeln zu erleichtern und auch eine visuelle Referenz bereitzustellen.

Die Tage werden in einer Tabelle mit 7 Spalten und 6 Zeilen abgelegt. Wir müssen diese Zahlen auch für Titel und Helferzellen um 2 erhöhen. Während die Spalten- und Zeilentitel Wochentage für Spalten und Wochennummern für Zeilen enthalten, sind die Helferzellen fortlaufende Zahlen ab 1.

Der Kalender könnte wie folgt aussehen.

Verwenden Sie Rahmen oder Hintergrundfarben nach Ihren Wünschen. Der wichtige Punkt ist die Verwendung einer dynamischen Wochennummer im Titel. Referenzieren Sie die WEEKNUM-Funktion für die erste Woche und addieren Sie 1 für jede aufeinanderfolgende Woche.

Erstellen wir weiterhin einen Kalender in Excel, indem wir die Tageszahlen auffüllen.

Tage generieren

Wenn Sie ein ähnliches Layout verwenden und sich nicht um die Details der Formel kümmern möchten, können Sie die folgende Formel kopieren. Fügen Sie es dann in die obere linke Zelle im Tagesbereich Ihres Kalenders ein und füllen Sie die verbleibenden Zellen aus. Die Zellen G2 und E4 verweisen auf die ersten Zellen der Helferspalten. Daher müssen Sie diese Referenzen möglicherweise anpassen, es sei denn, die erste Zelle ist G4.

Schauen wir uns nun die Formel genauer an. Erstens verwendet die Formel die Datumsfunktion, die ein Datum nach Jahr, Monat und Tag zurückgibt. Die ersten 2 Argumente werden von den Variablen YearNumber und MonthNumber geliefert, die bereits berechnet wurden.

Der Tagteil der Formel zielt darauf ab, Tageszahlen auf einer 7-Basis zu generieren, da es 7 Tage in einer Woche gibt. 7 * ($ E4-1) Teil der Formel Überprüfen Sie die Wochennummer in der Hilfsspalte E und generieren Sie eine Basis, die mit 0 beginnt und jedes Mal um 7 ansteigt.

Wir können diese Zahl um den Unterschied zwischen dem Wochentag des ersten Tages des Monats um die Wochennummer des Tages erhöhen oder verringern. Mit anderen Worten, wir bekommen den ersten Tag des gegenwärtigen Monats als Ankerpunkt. Zum Beispiel ist es der 7. Tag der Woche (in der Basis von Montag bis Sonntag) für den 1. November 2020.

Wenn die Woche unseres Kalenders davon ausgeht, dass Montag der erste Tag der Woche ist, sollte Montag 6 Tage vor (= 1-7) dem ersten Tag des Monats liegen. So erhalten wir Zahlen wie unten.

Addieren Sie diese Werte zu 1, um die Zahlen zu verschieben. So kann der Sonntag wie im folgenden Beispiel der erste Tag werden.

Als Ergebnis haben wir 2 Arrays für 2 Dimensionen. Um eine Tabelle zu erstellen, müssen wir beide Arrays addieren.

Diese Zahlen repräsentieren den Tag des aktuellen Monats. Es gibt Zahlen kleiner als 1 oder größer als 31, die offensichtlich keine Tage des Monats sein können.

Die Datumsfunktion verarbeitet diese Arten von Zahlen, indem sie ein Datum im Vormonat oder im nächsten Monat generiert. Zum Beispiel wird -2 zwei Tage vor dem ersten Tag des angegebenen Monats.

Wenn Sie diese Zahlen schließlich in der Datumsfunktion verwenden, können Sie die tatsächlichen Daten des aktuellen Monats abrufen.

Formatierung

Tageszahlen

Nun füllen wir die Tage. Sie können die Formatierung der Werte ändern, ohne den tatsächlichen Wert zu ändern. Diese Funktion wird als Zahlenformatierung bezeichnet. Befolgen Sie die Schritte, um die Zahlenformatierung so anzupassen, dass nur die Tage angezeigt werden.

1. Wählen Sie ein beliebiges Datum im Tagesbereich aus
2. Drücken Sie Strg + 1, um den Dialog Zellen formatieren zu öffnen
3. Aktivieren Sie die Registerkarte Nummer, wenn sie noch nicht geöffnet ist
4. Wählen Sie Benutzerdefiniert in der Kategorieliste
5. Geben Sie den Buchstaben d in das Feld Typ ein
6. Klicken Sie auf OK, um
   

Nun ist das Kalenderlayout fertig.

Wenn Sie den Abstand für einstellige Daten entfernen möchten, können Sie dd anstelle von single d verwenden. Um mehr über die Zahlenformatierung zu erfahren: Zahlenformatierung in Excel – Alles, was Sie wissen müssen

Hervorheben des heutigen Tages

Schließlich werden wir die bedingte Formatierung verwenden um verschiedene Farben für Tage festzulegen, die nicht im aktuellen Monat liegen, und den heutigen Tag hervorzuheben.

So fügen Sie diese bedingten Formatierungsoptionen hinzu;

1. Wählen Sie den Tagesbereich
2. Klicken Sie auf das Symbol Bedingte Formatierung auf der Registerkarte Start des Menübands
3. Neue Regel auswählen
   
4. Wählen Sie Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen
5. Geben Sie eine Formel ein, um die Regel zum Hervorheben des aktuellen Tages zu definieren
   =G4=TODAY()
   G4: Die obere linke Zelle des Bereichs enthält Tage. Verwenden Sie eine relative Referenz (nicht $ ).
6. Klicken Sie im Vorschaufenster des Dialogs Zellen formatieren auf die Schaltfläche Formatieren
   
7. Wählen Sie im Dialogfeld Formatierungsoptionen aus, die Sie in der Zelle des heutigen Tages sehen möchten
   Wir haben in diesem Beispiel einen orangefarbenen Hintergrund verwendet.
8. Klicken Sie auf OK, um anzuwenden
   
9. Im Vorschaufeld werden Ihre Einstellungen angezeigt
10. Klicken Sie auf OK, um die bedingte Formatierung anzuwenden
    

So sieht es aus:

Die Formel gibt einen booleschen Wert zurück. Wenn der Zellenwert (G4) gleich dem Ergebnis der Funktion HEUTE ist, die den aktuellen Tag in einem Datumsformat zurückgibt, gibt die Formel TRUE zurück. Andernfalls FALSCH. Wenn das Ergebnis WAHR ist, wendet Excel die Formatierung in die Zelle an.

Hier gibt es 2 wichtige Punkte:

1. Sie müssen eine relative Referenz verwenden, z. B. G4, es sei denn, Sie möchten nicht, dass Excel die Referenz entlang des Bereichs auffüllt. Überlegen Sie, wie sich Formeln ändern, wenn Sie sie an anderer Stelle kopieren. Excel wendet dasselbe auch für Formeln mit bedingter Formatierung an. Weitere Informationen finden Sie unter: So erstellen Sie eine Excel absolute Referenz und relative Referenz
2. Die Tageszellen müssen die tatsächlichen Datumswerte zurückgeben. Aus diesem Grund verwenden wir die Zahlenformatierung, um die Tagesportionen anzuzeigen.