In Excel-Arbeitsmappen werden oft Geburtstagslisten von Kolleginnen und Kollegen oder von Vereinsmitgliedern verwaltet. Wenn Sie das Geburtsdatum der einzelnen Personen in die Liste eintragen, können Sie schnell und einfach den nächsten Geburtstag aller Personen ermitteln.
In dem Beispiel soll für alle Personen der nächste Geburtstag ermittelt werden. Falls der nächste Geburtstag ein runder Geburtstag ist, soll dieser entsprechend gekennzeichnet werden. Dazu soll in einer separaten Spalte angezeigt werden, wie alt die Person werden wird.
Die Liste mit den Personen sieht folgendermaßen aus:
Um die nächsten Geburtstage zu ermitteln, gehen Sie bitte folgendermaßen vor:
- Aktivieren Sie die Zelle D2, um den nächsten Geburtstag der ersten Person zu ermitteln.
- Geben Sie die folgende Formel ein:
=WENN(DATUM(JAHR(HEUTE());MONAT(C2);TAG(C2))<HEUTE();DATUM(JAHR(HEUTE())+1;MONAT(C2);TAG(C2));DATUM(JAHR(HEUTE());MONAT(C2);TAG(C2)))
Mit dieser WENN-Formel wird geprüft, ob die Person im laufenden Jahr bereits Geburtstag gehabt hat oder nicht. Es wird Bezug genommen auf das aktuelle Tagesdatum, das Sie mit der Funktion HEUTE() ermitteln können. Um den Geburtstag der Person im aktuellen Jahr zu ermitteln, wird die Funktion DATUM() herangezogen. Die Funktion DATUM() dient dazu, aus dem Jahr, dem Monat und dem Tag ein Datum aufzubauen. Es wird das aktuelle Jahr mit JAHR(HEUTE()), der Monat und der Tag des Geburtsdatums verwendet. Falls die Person im aktuellen Jahr bereits Geburtstag gehabt hat, ist der folgende Ausdruck wahr:
DATUM(JAHR(HEUTE());MONAT(C2);TAG(C2))<HEUTE()
In diesem Fall ist der nächste Geburtstag dieser Person erst im nächsten Jahr. Der Geburtstag im Folgejahr wird ermittelt durch die folgende Formel:
DATUM(JAHR(HEUTE())+1;MONAT(C2);TAG(C2))
Hat die Person noch in diesem Jahr Geburtstag, wird folgende Formel verwendet:
DATUM(JAHR(HEUTE());MONAT(C2);TAG(C2))
- Formatieren Sie die Zelle D2 mit dem Datumsformat, falls dort eine ganze Zahl erscheinen sollte.
- Kopieren Sie die Formel nach unten, indem Sie doppelt auf das Ausfüllkästchen am unteren, rechten Rand der Zelle klicken.
- Aktivieren Sie die Zelle E2, um zu berechnen, wie alt die Person an ihrem nächsten Geburtstag werden wird.
- Geben Sie die folgende Formel ein:
=JAHR(D2)-JAHR(C2)
- Kopieren Sie die Formel ebenfalls nach unten.
- Im nächsten Schritt sollen die runden Geburtstage durch eine besondere Formatierung hervorgehoben werden. Dazu wird die bedingte Formatierung herangezogen. Markieren Sie den Zellbereich F2:F6 mit den Altersangaben.
- Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Formatvorlagen auf die Schaltfläche Bedingte Formatierung.
- Klicken Sie auf den Menüpunkt Neue Regel.
- Wählen Sie im Listenfeld den Regeltyp Formel zur Ermittlung der zu formatierenden Zeilen verwenden aus. Es erscheint das folgende Dialogbild:
- Geben Sie in dem Eingabefeld die folgende Formel ein:
=REST($E2;10)=0
Mit der Formel wird geprüft, ob bei der Division des Alters durch 10 ein Rest bleibt. Falls dieser Rest 0 beträgt, handelt es sich um einen runden Geburtstag. Achten Sie bitte besonders auf die Dollarzeichen als Kennzeichen für absolute Zellbezüge und gemischt relativ/absolute Bezüge.
- Legen Sie das gewünschte Zellformat fest, indem Sie auf die Schaltfläche Formatieren klicken.
- Wählen Sie beispielsweise einen gelben Hintergrund aus.
- Klicken Sie danach auf die Schaltfläche OK, um das Fenster Zellen formatieren zu schließen.
- Klicken Sie abschließend auf die Schaltfläche OK, um die bedingte Formatierung anzuwenden.
- Abschließend können Sie die Liste nach dem nächsten Geburtstag aufsteigend sortieren. Aktivieren Sie dazu eine beliebige Zelle in der Spalte D.
- Klicken Sie im Menüband auf der Registerkarte Daten in der Gruppe Sortieren und Filtern auf die Schaltfläche Nach Datum sortieren (aufsteigend).
Das Tabellenblatt sieht jetzt folgendermaßen aus:
Hinweis
Würde jemand am 29. Februar in einem Schaltjahr geboren, würde der nächste Geburtstag auf den 01. März des Jahres verlegt, falls es sich um kein Schaltjahr handelt. Obwohl in der Formel =DATUM(2015;2;29) ein ungültiges Datum angegeben wird, wird kein Fehler ausgegeben, sondern als Ergebnis der 01.03.2015.
Hallo,
ich möchte gerne wenn jemand heute Geburtstag hat, das dann sein Name in einer bestimmten Zelle steht.
Z.B.
A1 = Vorname B1= Nachname C1=Geburtstagsdatum P1= Frank hat heute Geburtstag
Wie kann ich das machen? Welche Formel benötige ich dafür?
Würde mich über ein Antwort sehr freuen.
Herzliche Grüße Frank
Hallo Frank,
folgende Formel könnte dies leisten:
=WENN(DATUM(JAHR(HEUTE());MONAT(C1);TAG(C1))= HEUTE();A1 & ” hat heute Geburtstag”;””)
super
sehr, vielen dank für diesen super Beitrag:-)
Top
Gut erklärt!
Wer aus der Liste als nächstes Geburtstag hat. (Ergebnis in einer Zelle ausgegeben) Der “Stichtag” in der Abbildung ist hier unnötig. Heute() ist aktueller bzw. erfüllt den Zweck sehr gut. Falls Stichtag Heute() nur abbildet, ist das unnötig (und verwirrend), um den Sachverhalt hier abzubilden.
Zeitersparnis
Zeitersparnis, weniger Stress, lehrreich!
Ich wäre nie auf die Formel gekommen. hat mir sher geholfen.
Ich suche eine bereits angelegte, jedoch fehlerhafte Geburtstagsliste
Sehr schöner Beitrag! Hat mir sehr viel Zeit und Mühe erspart, da ich nicht so gut mit dem Rechnen mit Daten in Excel vertraut bin.
Sehr gut erklärt, einfach umzusetzen.
Zeitersparnis
weniger Stress und es hat viel Zeit gespart Danke vielmals freundliche Grüße L.G.
Lerneffekt ist gelungen.
Sie haben gute Hinweise für das Anwenden der einzelnen Funktionen aufgezeigt.
Zeitersparnis
Zeitersparnis, ich hatte einen Syntaxfehler in der ersten WENN-Abfrage und habe diesen Fehler einfach nicht gesehen. Vielen Dank
Top
hat das Rechnen und programmieren mit Jahreszahlen und Exel verstänlicher gemacht.