Excel bietet einige sehr hilfreiche Funktionen für die Arbeit mit Datumsangaben. Eines davon ist das dynamische Kalenderraster. Wir können ein Startdatum haben, das der Anfang des Monats ist, und ein dynamisches Kalenderraster erstellen. Die Funktionen WEEKDAY und CHOOSE mit bedingter Formatierung helfen uns dabei. In diesem Tutorial erfahren Sie, wie Sie ein dynamisches Kalenderraster in Excel erstellen.
Abbildung 1. Beispiel für die Verwendung des dynamischen Kalenderrasters in Excel
Generische Formel
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Process
Hier verwenden wir die Funktionen CHOOSE und WEEKDAY. Diese Formel berechnet den Sonntag vor dem ersten Tag des Monats. Dies geschieht mithilfe der CHOOSE-Funktion. Es rollt auf die richtige Anzahl von Tagen zum vorherigen Sonntag zurück. WÄHLEN Sie die Zufallswerte für jeden Tag der Woche. Diese Funktion setzt null Tage zurück, wenn der erste Tag des Monats ein Sonntag ist. Es passt den Rollback für den Rest der Tage an.
Nachdem der erste Tag extrahiert wurde, fügen die anderen Formeln im Raster dem vorherigen Datum eine hinzu. Es überprüft die Zelle links auf einen Wert. Wenn kein Wert gefunden wird, wird ein Wert aus der Spalte ganz rechts in der obigen Zeile abgerufen. Wir weisen diesen Wert als gemischte Referenz zu. Wir tun dies, um sicherzustellen, dass die Spalte gesperrt ist, wenn die Formel in das Raster kopiert wird. Die gleiche Formel wird in allen anderen Zellen verwendet.
Daten einrichten
Das folgende Beispiel enthält ein Startdatum. Es ist in Zelle J5. Aus diesem Wert erstellen wir ein dynamisches Kalenderraster in den Spalten A bis G.
Dazu müssen wir:
- Wechseln Sie zur Registerkarte Ansicht im Menüband. Deaktivieren Sie Gitterlinien.
- Als nächstes müssen wir die Zellen A3 bis G9 auswählen. Wechseln Sie zur Registerkarte Start im Menüband. Klicken Sie auf Grenzen>Alle Grenzen.
- Klicken Sie auf Zelle A2. Weisen Sie die Formel = J6 zu.
- Rechtsklick auf A2. Wählen Sie Zellen formatieren>Zahlen>Benutzerdefiniert. Geben Sie im Feld Typ mmmm yyyy ein. OK.
- Wählen Sie A2 bis G2. Wählen Sie auf der Registerkarte Start die Option Zusammenführen und Zentrieren.
- Geben Sie die Wochentagsabkürzungen in die Zellen A3 bis G3 ein.
- Gehe zu Zelle A4. Wenden Sie die Formel an
=J6-CHOOSE(WEEKDAY(J6),0,1,2,3,4,5,6) - Wählen Sie die Zellen A4 bis G8 aus. Klicken Sie mit der rechten Maustaste und wählen Sie Zellen formatieren> Zahlen> Benutzerdefiniert. Geben Sie im Feld Typ d. Klicken Sie auf OK.
- Gehe zu Zelle B4. Wenden Sie die Formel
=IF(A4<>"",A4,$J6)+1an. Ziehen Sie es in die verbleibenden Zellen im Kalenderraster. - Wählen Sie die Zellen A4 bis G8 aus. Wechseln Sie auf der Registerkarte Start im Menüband zu Bedingte Formatierung. Als nächstes müssen wir auf Neue Regel klicken.
Abbildung 2. Beispiel für die Anwendung der bedingten Formatierung
- Wählen Sie Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.
- Klicken Sie auf das Feld Werte formatieren, bei denen diese Formel wahr ist. Weisen Sie im Feld Formel die Formel
=MONTH(A4)<>MONTH(J4)zu.
Abbildung 3. Anwenden der Formel auf das bedingte Format
- Klicken Sie auf die Registerkarte Format in der Nähe des Vorschaufelds.
- Als nächstes müssen wir zu Fill>Background Color und die Farbe auswählen, in der wir hervorheben möchten.
Abbildung 4. Verwalten der Anzeigeoptionen
- Klicken Sie zweimal auf OK.
- Um das aktuelle Datum zu formatieren, folgen Sie den Schritten 10 bis 14. Ändern Sie die Formel in
=A4=TODAY().
Abbildung 5. Anwenden des Formats auf Aktuelles Datum
Notizen
Wir können einen Kalender erstellen, der automatisch basierend auf dem aktuellen Datum aktualisiert wird. Dazu müssen wir die Formel =EOMONTH(TODAY(),-1)+1 in J6 verwenden. Diese Formel ruft das aktuelle Datum mit der Funktion HEUTE ab. Später wird der erste Tag des laufenden Monats mit EOMONTH . Indem wir TODAY() durch ein anderes Datum ersetzen, können wir einen Kalender in einem anderen Monat erstellen.
Meistens ist das Problem, das Sie lösen müssen, komplexer als eine einfache Anwendung einer Formel oder Funktion. Wenn Sie stundenlange Recherche und Frustration sparen möchten, probieren Sie unseren Live-Excelchat-Service aus! Unsere Excel-Experten stehen Ihnen 24/7 zur Verfügung, um jede Excel-Frage zu beantworten. Wir garantieren eine Verbindung innerhalb von 30 Sekunden und eine maßgeschneiderte Lösung innerhalb von 20 Minuten.