Tutorials

📅 Excel-Lookup: Wert finden, wenn Datum zwischen zwei Daten liegt (Jahre ignorieren)

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel Werte basierend auf einem Datumsbereich suchst und dabei das Jahr ignorierst (nützlich für Saisons, Sternzeichen oder wiederkehrende Ereignisse).

In Excel ist der Vergleich von Daten über Jahre hinweg knifflig, da Excel jedes Datum als fortlaufende Zahl (inklusive Jahr) speichert. Um nur Tag und Monat zu vergleichen, müssen wir alle Daten auf ein "virtuelles" gleiches Jahr normalisieren.

1. Die Ausgangssituation

Stell dir vor, du hast eine Tabelle mit Saison-Rabatten und möchtest für ein beliebiges Datum (z. B. ein Bestelldatum) herausfinden, in welche Saison es fällt.

Referenztabelle (Saisons): | Start (A) | Ende (B) | Saison (C) | | :--- | :--- | :--- | | 01.01. | 31.03. | Winter | | 01.04. | 30.06. | Frühling | | 01.07. | 30.09. | Sommer | | 01.10. | 31.12. | Herbst |

Suchwert:

  • Datum: 15.05.2024 -> Ergebnis sollte sein: Frühling

2. Der Trick: Das Jahr "neutralisieren"

Damit Excel das Jahr 2023, 2024 oder 2028 gleich behandelt, wandeln wir jedes Datum in das Jahr 2000 um. Warum 2000? Weil es ein Schaltjahr war (wichtig für den 29. Februar!).

Die Formel, um ein Datum in das Jahr 2000 zu portieren:

=DATUM(2000; MONAT(Zelle); TAG(Zelle))

3. Schritt-für-Schritt-Anleitung

Schritt A: Vorbereitung der Referenztabelle

Erstelle deine Tabelle (A2:C5) und stelle sicher, dass die Daten als echte Excel-Daten erkannt werden (auch wenn du nur Tag/Monat eingibst, ergänzt Excel automatisch das aktuelle Jahr – das korrigieren wir gleich in der Formel).

Schritt B: Die Suchformel (XVERWEIS oder FILTER)

Wir nutzen die Funktion FILTER oder XVERWEIS. Hier ist die Lösung mit der FILTER-Funktion (verfügbar in Microsoft 365 und Excel 2021), da sie am einfachsten zu lesen ist.

Angenommen, dein Suchdatum steht in Zelle E2.

Die Formel lautet:

=FILTER(C2:C5; 
  (DATUM(2000; MONAT(E2); TAG(E2)) >= DATUM(2000; MONAT(A2:A5); TAG(A2:A5))) * 
  (DATUM(2000; MONAT(E2); TAG(E2)) <= DATUM(2000; MONAT(B2:B5); TAG(B2:B5)))
)

Erklärung der Formel:

  1. DATUM(2000; MONAT(E2); TAG(E2)): Wandelt dein Suchdatum in das Jahr 2000 um.
  2. DATUM(2000; MONAT(A2:A5); TAG(A2:A5)): Wandelt alle Startdaten in das Jahr 2000 um.
  3. Das * Symbol fungiert als UND-Logik. Es prüft: Ist das Suchdatum >= Startdatum UND <= Enddatum?
  4. FILTER gibt den entsprechenden Wert aus Spalte C zurück.

4. Alternative: Die klassische Methode (SVERWEIS)

Wenn du kein Microsoft 365 hast, kannst du eine Hilfsspalte nutzen:

  1. Erstelle eine neue Spalte links neben deiner Tabelle.
  2. Berechne dort den "Jahres-neutralen" Startwert: =DATUM(2000; MONAT(A2); TAG(A2)).
  3. Sortiere diese Tabelle aufsteigend nach der neuen Hilfsspalte.
  4. Nutze den SVERWEIS mit dem Parameter WAHR (ungefähre Übereinstimmung):
    =SVERWEIS(DATUM(2000; MONAT(E2); TAG(E2)); Hilfsspalte:SaisonSpalte; 2; WAHR)

5. Sonderfall: Zeitspannen über den Jahreswechsel

Wenn ein Zeitraum z. B. vom 15.12. bis 15.01. geht, funktioniert die obige Logik nicht direkt, da der Startwert (Dezember) größer ist als der Endwert (Januar).

Lösung: Du musst den Zeitraum in zwei Zeilen aufteilen:

  1. 15.12. bis 31.12.
  2. 01.01. bis 15.01.

Oder du nutzt eine erweiterte WENN-Logik, aber die Aufteilung in zwei Zeilen ist die sauberste Methode für Excel-Lookups.

Zusammenfassung der Vorteile

  • Dynamisch: Funktioniert für jedes Jahr (2024, 2025, etc.).
  • Schaltjahr-sicher: Durch die Verwendung des Jahres 2000 wird auch der 29. Februar korrekt verarbeitet.
  • Übersichtlich: Keine komplizierten verschachtelten WENN-Funktionen nötig.
0