Wer Geburtstage, Kundenlisten oder Personaldaten in Excel verwaltet, möchte manchmal automatisch das passende Sternzeichen zu einem Datum ausgeben lassen. Mit einer einzigen Formel können Sie aus einem Geburtsdatum direkt das zugehörige Sternzeichen ableiten – ganz ohne manuelles Nachschlagen.
In diesem Beitrag zeige ich Ihnen:
- wie Sie das Sternzeichen automatisch berechnen
- wie die Formel funktioniert
- welche Alternativen es gibt
- wie Sie sogar das Sternzeichen-Symbol ausgeben können
- wie Sie Testdaten erzeugen
- wie Sie typische Fehler vermeiden
- wie Sie Power Query und moderne Excel-Funktionen nutzen
| Hinweis: Die verwendeten Datumsgrenzen entsprechen den üblichen astrologischen Standardwerten. Je nach Quelle können diese geringfügig variieren. |
Inhaltsverzeichnis
Beispiel
Angenommen, in Zelle A2 steht ein Geburtsdatum:
- Spalte: Datum
- Wert: 15.04.1990
Als Ergebnis soll Excel ausgeben: Widder
Lösung mit einer Formel
Formel:
| =VERWEIS(MONAT(A2)*100+TAG(A2);{101;120;219;321;421;521;622;723;824;924;1024;1123;1222};{„Steinbock“;“Wassermann“;“Fische“;“Widder“;“Stier“;“Zwillinge“;“Krebs“;“Löwe“;“Jungfrau“;“Waage“;“Skorpion“;“Schütze“;“Steinbock“}) |
Die Formel gibt abhängig vom Datum automatisch das richtige Sternzeichen zurück. Die zugrunde liegenden Grenzwerte entsprechen den üblichen astrologischen Zeiträumen und funktionieren dank einer mathematischen Kennzahl auch in Schaltjahren robust.
Warum steht „Steinbock“ zweimal in der Liste? Weil das Sternzeichen den Jahreswechsel überspannt (22.12. bis 19.01.). VERWEIS braucht deshalb den Startwert 101 für den Januar-Anteil und 1222 für den Dezember-Anteil.
| Wichtig: Die Suchmatrix in der VERWEIS-Funktion muss aufsteigend sortiert sein, damit das Ergebnis korrekt ist. |
| Tipp für die Praxis: Um Fehlermeldungen bei leeren Zellen oder ungültigen Einträgen zu vermeiden, empfehle ich folgende robuste Variante: |
| =WENN(ISTZAHL(A2); VERWEIS(MONAT(A2)*100+TAG(A2);{101;120;219;321;421;521;622;723;824;924;1024;1123;1222};{„Steinbock“;“Wassermann“;“Fische“;“Widder“;“Stier“;“Zwillinge“;“Krebs“;“Löwe“;“Jungfrau“;“Waage“;“Skorpion“;“Schütze“;“Steinbock“}); „“) |
Wie funktioniert die Formel?
Schritt 1: Monat und Tag als Kennzahl bestimmen
Der Ausdruck MONAT(A2)*100+TAG(A2) ermittelt eine feste Kennzahl für das Datum (Monat mal 100 plus Tag). Dadurch entsteht ein numerischer Wert, der unabhängig vom Jahr logisch verglichen werden kann. Das Jahr selbst spielt für die Berechnung keine Rolle – es fließen ausschließlich Monat und Tag ein, weshalb die Formel für jedes Geburtsjahr gleichermaßen funktioniert.
Beispiele:
- Datum: 01.01. → Kennzahl: 101 → Steinbock
- Datum: 19.01. → Kennzahl: 119 → Steinbock
- Datum: 20.01. → Kennzahl: 120 → Wassermann
- Datum: 18.02. → Kennzahl: 218 → Wassermann
- Datum: 19.02. → Kennzahl: 219 → Fische
- Datum: 15.04. → Kennzahl: 415 → Widder
- Datum: 29.02.2024 → Kennzahl: 229 → Fische
- Datum: 31.12. → Kennzahl: 1231 → Steinbock
Dadurch kann Excel das Datum präzise mit den Startpunkten der einzelnen Sternzeichen vergleichen – auch in Schaltjahren.
Schritt 2: Sternzeichen über VERWEIS ermitteln
Die Funktion VERWEIS durchsucht die Liste der Start-Kennzahlen:
| {101;120;219;321;421;521;622;723;824;924;1024;1123;1222} |
Diese Werte repräsentieren die jeweiligen Beginn-Daten der Sternzeichen innerhalb eines Jahres (z. B. 321 für den 21. März). Anschließend liefert Excel den passenden Text aus der zweiten Liste zurück.
Übersicht der Sternzeichen
- Steinbock: 22.12. – 19.01.
- Wassermann: 20.01. – 18.02.
- Fische: 19.02. – 20.03.
- Widder: 21.03. – 20.04.
- Stier: 21.04. – 20.05.
- Zwillinge: 21.05. – 21.06.
- Krebs: 22.06. – 22.07.
- Löwe: 23.07. – 23.08.
- Jungfrau: 24.08. – 23.09.
- Waage: 24.09. – 23.10.
- Skorpion: 24.10. – 22.11.
- Schütze: 23.11. – 21.12.
Moderne Lösung mit XVERWEIS
Verwenden Sie Excel 365 oder Excel 2021, können Sie die Formel auch mit XVERWEIS aufbauen. Dafür legen Sie eine kleine Hilfstabelle an.
In Spalte F stehen die Start-Kennzahlen, in Spalte G die Sternzeichen.
Anschließend genügt:
| =XVERWEIS(MONAT(A2)*100+TAG(A2);F2:F14;G2:G14;““;-1) |
| Hinweis: Der Parameter -1 sorgt dafür, dass Excel automatisch das nächstkleinere Element verwendet. Diese Variante ist deutlich einfacher zu warten und anzupassen. Microsoft empfiehlt XVERWEIS inzwischen als Nachfolger von VERWEIS. |
Noch übersichtlicher mit LET (Excel 365):
| =LET( kennzahl; MONAT(A2)*100+TAG(A2); XVERWEIS(kennzahl; F2:F14; G2:G14; „“; -1) ) |
| Tipp: In Excel 365 können Sie die Formel einfach nach unten kopieren oder als dynamische Berechnung über ganze Spalten verwenden. |
Alternative mit WENNS(für bessere Nachvollziehbarkeit)
| =WENNS( MONAT(A2)*100+TAG(A2)>=1222;“Steinbock“; MONAT(A2)*100+TAG(A2)>=1123;“Schütze“; MONAT(A2)*100+TAG(A2)>=1024;“Skorpion“; MONAT(A2)*100+TAG(A2)>=924;“Waage“; MONAT(A2)*100+TAG(A2)>=824;“Jungfrau“; MONAT(A2)*100+TAG(A2)>=723;“Löwe“; MONAT(A2)*100+TAG(A2)>=622;“Krebs“; MONAT(A2)*100+TAG(A2)>=521;“Zwillinge“; MONAT(A2)*100+TAG(A2)>=421;“Stier“; MONAT(A2)*100+TAG(A2)>=321;“Widder“; MONAT(A2)*100+TAG(A2)>=219;“Fische“; MONAT(A2)*100+TAG(A2)>=120;“Wassermann“; WAHR;“Steinbock“ ) |
Diese Variante ist leichter zu verstehen, aber deutlich schlechter wartbar.
| Wichtig: Prüfen Sie immer vom größten zum kleinsten Wert, sonst greift die erste wahre Bedingung zu früh. |
Sternzeichen-Symbole ausgeben
Neben dem Namen können Sie auch das passende Symbol anzeigen. Die Symbole lassen sich über UNICHAR erzeugen:
- Widder: =UNIZEICHEN(9800)
- Stier: =UNIZEICHEN(9801)
- Zwillinge: =UNIZEICHEN(9802)
- Krebs: =UNIZEICHEN(9803)
- Löwe: =UNIZEICHEN(9804)
- Jungfrau: =UNIZEICHEN(9805)
- Waage: =UNIZEICHEN(9806)
- Skorpion: =UNIZEICHEN(9807)
- Schütze: =UNIZEICHEN(9808)
- Steinbock: =UNIZEICHEN(9809)
- Wassermann: =UNIZEICHEN(9810)
- Fische: =UNIZEICHEN(9811)
Die Codes 9800 bis 9811 entsprechen den Unicode-Zeichen für Widder bis Fische. In Excel 365 können Sie alle zwölf Symbole auf einmal erzeugen mit:
| =UNIZEICHEN(SEQUENZ(12;1;9800)) |
| Tipp: Stellen Sie die Schriftart auf „Segoe UI Symbol“ oder „Arial Unicode MS“, damit die Zeichen nicht als Kästchen erscheinen. |
Dynamische Kombination aus Name + Symbol
Ohne Hilfstabelle geht es mit LET in einer Formel:
| =LET( k; MONAT(A2)*100+TAG(A2); idx; VERGLEICH(k;{101;120;219;321;421;521;622;723;824;924;1024;1123;1222};1); INDEX({„Steinbock“;“Wassermann“;“Fische“;“Widder“;“Stier“;“Zwillinge“;“Krebs“;“Löwe“;“Jungfrau“;“Waage“;“Skorpion“;“Schütze“;“Steinbock“};idx) & “ “ & UNIZEICHEN(INDEX({9809;9810;9811;9800;9801;9802;9803;9804;9805;9806;9807;9808;9809};idx)) ) |
Typische Fehlerquellen
Datum ist als Text gespeichert:
Wenn Excel das Datum linksbündig anzeigt, handelt es sich häufig um Text statt um ein echtes Datum. Prüfen Sie dies mit:
| =ISTZAHL(A2) |
Das Ergebnis muss WAHR sein.
| Tipp: Mit =DATWERT(A2) können Sie Text in ein Datum umwandeln. Bei importierten CSV-Dateien hilft oft: =DATWERT(WECHSELN(A2;“.“;“/“)) |
Ungültige Datumswerte (z. B. 31.02.):
| =WENNFEHLER(XVERWEIS(…);“Ungültiges Datum“) |
Falsche regionale Einstellungen:
Achten Sie darauf, dass Excel das Datum korrekt interpretiert. Beispielsweise kann 03.04.2025 je nach Einstellung unterschiedlich verstanden werden.
Testdaten erzeugen
Um die Formeln schnell zu testen, eignen sich dynamische Listen:
Alle Tage eines Jahres:
| =SEQUENZ(365;1;DATUM(2024;1;1);1) |
Nur Monats-/Tageskombinationen:
| =TEXT(SEQUENZ(12;1;1;30);“TT.MM.“) |
Hinweis: SEQUENZ steht – ebenso wie LET – nur in Excel 365 zur Verfügung. In älteren Excel-Versionen erzeugen Sie eine vergleichbare Datumsreihe stattdessen über das Ausfüllen per Ziehen.
Praxisbeispiel: Kundenliste mit Sternzeichen
Gerade im Marketing- oder CRM-Bereich kann die automatische Ermittlung des Sternzeichens hilfreich sein. Beispielsweise können Sie:
- Geburtstagslisten erweitern
- personalisierte Auswertungen erstellen
- Kunden nach Geburtszeitraum gruppieren
- Geburtstagsaktionen vorbereiten
Durch die automatische Berechnung bleiben Ihre Auswertungen jederzeit aktuell. Bei sehr großen Listen empfiehlt es sich, die Kennzahl in einer Hilfsspalte zu berechnen und per XVERWEIS darauf zuzugreifen.
| Extra-Tipp: Nutzen Sie bedingte Formatierung mit der Regel =$B2=“Widder“, um zum Beispiel alle Widder-Zeilen automatisch farblich hervorzuheben. |
| Datenschutz-Hinweis: Geburtsdaten gelten als personenbezogene Daten im Sinne der DSGVO. Stellen Sie bei der Auswertung von Kunden- oder Personaldaten sicher, dass eine entsprechende Rechtsgrundlage vorliegt und die Daten ausschließlich zweckgebunden verwendet werden. |
Alternative mit Power Query
Bei sehr großen Datenmengen legen Sie in Power Query eine bedingte Spalte mit denselben Grenzwerten an. Vorteil: keine volatile Formel im Blatt und deutlich bessere Performance.
Beispiel für eine bedingte Spalte:
- Wenn Kennzahl ≥ 1222 dann „Steinbock“
- sonst wenn Kennzahl ≥ 1123 dann „Schütze“
- … usw.
Hier geht es zur Power Query-Serie von excel-meister.de
Vorlage herunterladen
Damit Sie nicht alles abtippen müssen, finden Sie hier eine fertige Beispieldatei mit allen Varianten, Symbolen und bedingter Formatierung zum Download.
Häufige Fragen
Funktioniert das in Excel Online?
Ja, VERWEIS, XVERWEIS und LET werden unterstützt.
Warum weicht mein Ergebnis um einen Tag ab?
Die Grenzen variieren je nach astrologischer Quelle. Passen Sie die Startwerte in der Hilfstabelle einfach an.
Kann ich die Formel auch für andere Sternzeichen-Systeme anpassen, z. B. das chinesische Tierkreiszeichen?
Nicht direkt. Das chinesische Tierkreiszeichen richtet sich nicht nach Tag und Monat, sondern nach dem Geburtsjahr gemäß dem chinesischen Mondkalender. Die hier gezeigte Tag-Monat-Logik lässt sich dafür nicht übertragen; Sie benötigen eine andere Berechnungsgrundlage auf Basis des Jahres.
Hinweis zur Verwendung
| Hinweis zur Verwendung: Sternzeichen dienen hier ausschließlich zur Demonstration von Datumslogik in Excel und sind nicht für Personalentscheidungen geeignet. |
Fazit
Mit einer Excel-Formel können Sie aus einem Datum automatisch das passende Sternzeichen ermitteln. Besonders bei Geburtstagslisten, Kundenstammdaten oder Auswertungen spart dies viel Zeit.
Für moderne Excel-Versionen empfiehlt sich die Verwendung einer Hilfstabelle in Kombination mit XVERWEIS oder LET. Dadurch wird die Lösung übersichtlicher, robuster und einfacher zu pflegen.
Weiterführende Links
Wenn Sie die im Beitrag verwendeten Excel-Funktionen und Techniken vertiefen möchten, finden Sie hier einige empfehlenswerte Quellen:
Excel-Funktionen
- Microsoft: XVERWEIS-Funktion
Offizielle Dokumentation zur modernen Nachschlagefunktion XVERWEIS. Microsoft empfiehlt sie als Nachfolger von SVERWEIS. - Microsoft: LET-Funktion
Erfahren Sie, wie Sie Formeln durch Variablen übersichtlicher und schneller gestalten können. - Microsoft: Excel-Funktionen nach Kategorie
Vollständige Übersicht aller Excel-Funktionen mit Beschreibungen.
Tabellen und strukturierte Verweise
- Microsoft: Strukturierte Verweise in Excel-Tabellen
Ideal, wenn Sie Sternzeichen automatisch in wachsenden Kunden- oder Mitarbeiterlisten berechnen möchten.
Unicode und Sternzeichen-Symbole
- Unicode-Tabelle der Sternzeichen-Symbole
Übersicht aller Unicode-Zeichen, einschließlich der Tierkreiszeichen.
Astrologische Informationen
- Wikipedia: Tierkreiszeichen (Sternzeichen)
Hintergrundinformationen zu den astrologischen Sternzeichen und ihren Zeiträumen.
Navigation innerhalb der Formel-Serie
- ⬅️ Vorheriger Beitrag: F7 – Wievielter Tag des Jahres in Excel? Tagesnummer einfach berechnen
- ➡️ Nächster Beitrag:
- 🧭 Zur Übersicht: Excel Formeln & Berechnungen – Praxisbeispiele und Funktionen verständlich erklärt
Austausch & Fragen
Schreiben Sie mir gerne auf Linkedin, wie Ihre Erfahrungswerte in Excel sind – ich freue mich auf den Austausch. Den Link zu meiner LinkedIn-Gr