Tipps und Tricks rund um Excel

A	B	C	D	E	F	G	H	I/J	K
L	M	N	O	P/Q	R	S	T	U-W	X-Z

 

Datum - Kalender selber bauen

1. Datumsformel zusammenbauen
2. Monat ermitteln
3. Formel für das Ausfüllen nach unten vorbereiten
4. Formel nach unten und rechts ausfüllen
5. Formatieren des Kalenders
6. Wochenende hervorheben
7. Styling

 

Mit ein paar Tricks, dem Wissen um das Formatieren von Datumsangaben, bedingten Formaten und einer Formel aus den Datumsfunktionen, können Sie sich einen Kalender basteln. Der funktioniert über viele Jahre.

Das Jahr können Sie entweder als feste Zahl in B1 schreiben oder mit =JAHR(HEUTE()) dynamisch ermitteln.

So geht es Schritt für Schritt:

Schritt 1: Datumsformel zusammenbauen

Die Funktion DATUM() kann ein Datum aus Einzelbestandteilen zusammenbauen. Die Funktion sieht so aus:

=DATUM(Jahr;Monat;Tag)

Begonnen wir mit dem Datum in Zeile 3 - die Monatsnamen in Zeile 2 kommen später dran. Einfach ist das Jahr zu ermitteln, es steht immer in B1 - wir brauchen also den absoluten Bezug auf B1:

=DATUM($B$1;Monat;Tag)

Der Monat entspricht immer der Spalte: Januar in Spalte 1, Februar in Spalte 2, März in Spalte 3 etc.; die Funktion SPALTE() gibt die aktuelle Spaltennummer aus:

=DATUM($B$1;SPALTE();Tag)

Der Tag entspricht der Zeilennummer abzüglich zwei Zeilen für die Überschriften. Die Zeilennummer ermitteln Sie mit ZEILE():

=DATUM($B$1;SPALTE();ZEILE()-2)

Für das erste Datum in A3 sieht die Formel so aus. Sie können Sie probeweise nach unten ausfüllen. Aber füllen Sie sie noch nicht komplett aus - wir müssen noch eine Ergänzung machen. Dafür brauchen Sie aber zuerst den Monat in Spalte A.

 

Schritt 2: Monat ermitteln

Schreiben Sie in A2 nur den Bezug =A3 - damit wird zuerst genau das gleiche wie in Zelle A3 geschrieben: das Datum 01.01.2012 erscheint hier erneut. Lassen Sie A2 markiert und rufen Sie auf Start / Zahl / Benutzerdefiniert und geben Sie ein MMMM. Das bedeutet, dass das Datum nur als Monatsname ausgegeben werden soll.

Achten Sie darauf, dass Sie tatsächlich den Großbuchstaben M verwenden und keinen kleinen! Sonst gibt es statt der Monatsnamen die aktuellen Minuten. Bestätigen Sie mit OK. Jetzt muss in A2 Januar stehen.

 

Schritt 3: Die Formel für das Ausfüllen nach unten vorbereiten

Wenn Sie die Formel für das Datum in A3 einfach so nach unten ausfüllen, müssen Sie sehr gut aufpassen, dass Sie nicht über den Monatsletzen hinausziehen. Excel setzt sonst in der Januarspalte mit dem 01.02.2008, 02.02.2008 etc. fort!

Um das zu vermeiden, muss die Formel ab A4 verändert werden. Die Formel soll nicht nur das Datum ermitteln, sondern auch noch mit dem Monatsnamen aus A2 vergleichen. Nur solange der Monat aus der Formel gleich ist mit dem Monatsnamen in A2, soll das Datum ermittelt werden.

Beginnen Sie erst in Zelle A4 - nicht bereits in A3!

 

1. Klicken Sie in der Bearbeitenzeile zwischen das = und DATUM,
2. ergänzen Sie WENN(MONAT(
3. Klicken Sie das Ende der jetzigen Formel hinter ...ZEILE()-2)
4. und ergänzen Sie )=MONAT(A$2);

Sind Sie noch dabei? Gut! Dann geht es jetzt in der Formel weiter:

Markieren Sie von Datum($B$1; bis ()-2) alles, was zur Datumsformel gehört und kopieren Sie es in die Zwischenablage - am einfachsten Strg+C drücken

5. BewegenSie den Cursor hinter das Semikolon nach =MONAT($A$2); und fügen Sie den kopierten Teil der Formel ein - am einfachsten mit Strg+V
6. Schreiben Sie zum Schluss noch ein Semikolon und zwei Anführungszeichen dafür, dass ansonsten Nichts geschrieben werden soll und schließen Sie die Formel mit der Klammer

Jetzt muss Ihre Formel komplett so aussehen (alles in einer Zeile):

=WENN(MONAT(DATUM($B$1;SPALTE();ZEILE()-))=MONAT(A$2);DATUM($B$1;SPALTE();ZEILE()-2);"")

Und so lesen Sie die Formel:

• Wenn der Monat (WENN(MONAT
• aus dem Datum Jahr/Monat/Tag (DATUM($B$1;SPALTE();ZEILE()-2)
• identisch ist mit dem Monat aus der Zelle A2 (=MONAT(A$2))
• dann schreibe das Datum (DATUM($B$1;SPALTE();ZEILE()-2)
• ansonsten schreibe nichts ("")

 

Schritt 4: Die Formeln nach unten und dann nach rechts ausfüllen

Sie haben jetzt folgenden Stand:

• In B1 steht das Jahr 2008
• In A2 steht der Monat Januar, ermittelt mit =A3
• In A3 steht die erste Formel =DATUM($B$1;SPALTE();ZEILE()-2)
• In A4 steht die zweite Formel =WENN(MONAT(DATUM($B$1;SPALTE();ZEILE()-2))=MONAT(A$2);DATUM($B$1;SPALTE();ZEILE()-2);"")

Jetzt geht es weiter mit dem Ausfüllen der Formeln. Zuerst geht es nach unten, dann nach rechts: Klicken Sie in A4 Zeigen Sie auf das Ausfüllkästchen und ziehen Sie die Formel nach unten

• Auf das Monatsende müssen Sie nicht achten, Excel hört auf, wenn das Monatsende erreicht wurde
• Sie sollten aber auf jeden Fall bis zum 31. Januar ziehen!

Dank unserer komplizierten WENN-DANN-Formel haben Sie leere Zellen, wenn Sie über den 31.01. hinausziehen!

Markieren Sie die Zellen von A2 bis A33 (dort sollte der 31.01.2012 stehen). Zeigen Sie wieder auf das Ausfüllkästchen und ziehen Sie jetzt alles gemeinsam nach rechts, bis Sie den Dezember erreichen. Wenn es zu viele Monat werden, löschen Sie später die überzähligen Spalten.

 

Schritt 5: Formatieren des Kalenders

Was Sie im Moment sehen, ist nicht wirklich übersichtlich. Die Datumsangaben sollen nur noch den Tag in zwei Stellen (03 für den Dritten) und den Tagesnamen abgekürzt zeigen. Der 03.01.2008 soll also als 03 Do erscheinen. Da der Monatsname ja oben drüber steht, brauchen wir den Monat weder als Zahl noch als Ziffer.

Markieren Sie von A3 bis L33 alle Datumsangaben; markieren Sie den Monatsnamen in Zeile 2 nicht mit! Wählen Sie Start / Zahl / Benutzerdefiniert Tippen Sie als Format ein TT TTT (das bedeutet: TT für 03 (Zahl, zwei Stellen) TTT für Di (abgekürzter Tagesname) Bestätigen Sie mit OK

Lassen Sie den Bereich A3 bis L33 markiert und wählen Sie Start / Ausrichtung / Linksbündig

Soweit zufrieden? Dann brauchen wir noch die Hervorhebung der Wochenenden.

 

Schritt 6: Wochenenden hervorheben

Was ist ein Wochenende? Samstag und Sonntag, das sind je nach Zählweise entweder der siebte (Samstag) und der erste (Sonntag) Tag oder der sechste (Samstag) und siebte (Sonntag) Tag. Letzteres ist für uns einfacher zu handhaben: alle Tage, die größer sind als 5 sind Wochenende. Wir müssen Excel nur veranlassen, die Wochentage als Zahl von 1 bis 7 auszugeben - das geht mit der Funktion WOCHENTAG:

=WOCHENTAG(Datum;Typ)

• Typ 1 beginnt die Woche am Sonntag: Samstag ist der Tag 7, Sonntag der Tag 1
• Typ 2 beginnt die Woche am Montag: Samstag ist der Tag 6, Sonntag der Tag 7

Mit dieser Funktion bauen wir eine Bedingte Formatierung auf. Markieren Sie wieder den Bereich von A3 bis L33 mit allen Datumsangaben, ohne die Monatsnamen der Zeile 2.

• Wählen Sie Start / Formatvorlagen / Bedingte Formatierung / Neue Regel
• Klicken Sie auf Formel zur Ermittlung der zu formatierenden Zellen verwenden

Klicken Sie in Werte formatieren, für die diese Formel wahr ist Tippen Sie die Formel ein =WOCHENTAG(A3;2)>5

• Klicken Sie auf Formatieren und dann auf Ausfüllen, wählen Sie eine Farbe
• Bestätigen Sie mit OK und noch mal mit OK

Schritt 7: Styling

Jetzt gibt es nur noch Feinarbeiten: die Monatsnamen fett machen, die Spalten evtl. breiter ziehen, Rahmenlinien ziehen - was Sie mögen! Tja, und die Feiertage müssen Sie noch selber hervorheben - das geht automatisch sicherlich auch, aber der Aufwand ist ein bisschen zu hoch.

Der Jahreskalender ist jetzt fertig für alle Jahre. Tippen Sie statt 2012 ein anderes Jahr in B1 und Sie erhalten den Kalender für dieses Jahr oder verwenden Sie die Formel =JAHR(HEUTE()), um immer das aktuelle Jahr zu ermitteln.

Herunterladen können Sie den Kalender natürlich auch - mit Tabellen, die von Schritt 1 bis 7 alles nacheinander zeigen. Und natürlich auch als fertiges Exemplar. Aber Sie lernen mehr, wenn Sie ihn selber zusammenbauen. Bestimmt!

23.08.2012