Anzahl der Tage bis zum Termin
Die Aufgabe scheint ganz einfach zu sein: „Wie viele Tage sind es noch bis zum nächsten Geburtstag?” Solch eine Tabelle ist rasch angelegt und im Prinzip heißt das ja:
Tag_Der_Geburt_Aktuelles_Jahr – HEUTE().
Das haut aber nur im Prinzip hin, nämlich immer nur dann, wenn der diesjährige Geburtstag noch in der Zukunft liegt. In diese Datei habe ich das Geburtsdatum in Zelle B2 so gelegt, dass das Ergebnis garantiert korrekt ist. Aber der Geburtstag ist ja nicht immer Silvester, er muss durchaus öfter einmal angepasst werden … 😛
Für die Lösung bieten sich mehrere Möglichkeiten an. In Zeile 2 der Tabelle sind alle Werte von Hand eingegeben, was zwar zum gewünschten Erfolg führt aber nicht wirklich sinnvoll ist. Hier muss dann natürlich auch noch beachtet werden, dass das Jahr immer so angepasst wird, dass der nächste Geburtstag garantiert in der Zukunft liegt. Damit gilt: Unbrauchbar.
Ein gewisser „Automatismus” ist in Zeile 3 verwirklicht. Hier wird auf den eigentlichen Geburtstag in Zelle B2 Bezug genommen und das aktuelle Jahr für die Berechnung der Differenz per Funktion ersetzt. Aber auch diese Formel krankt daran, dass der diesjährige Geburtstag eventuell noch nicht gewesen ist. Wenn Sie nämlich das Datum in B2 ändern, kann es zu einer unangenehmen Überraschung kommen.
Hinweis: Der in dieser und den folgenden Zeilen verwendete Bezug auf den Geburtstag in Zelle B2 ist der Übersicht und Klarheit halber mit einem Bereichsnamen (GebTag) verwirklicht worden. Beachten Sie aber unbedingt, dass Sie sinnvollerweise die Zelladressen verwenden, wenn Sie die Formel nach unten kopieren (obwohl es auch unter gewissen Bedingungen mit einem Bereichsnamen geht).
In Zeile 4 ist die Grundlage die gleiche wie vorher, aber ich habe eine andere Berechnungsmethode verwendet. Statt der Subtraktion der beiden kalendarischen Daten ist die Funktion DateDif() verwendet worden. Diese hat beispielsweise den Vorteil, dass auch eine Ausgabe wie „3 Monate 9 Tage” möglich ist. – Da an der Grundlage bezüglich der Einschränkung, dass der Geburtstag in diesem Jahr noch folgt nichts verändert wurde, kann auch diese Formel zu Fehlern führen. Im Fehlerfall wäre dann eine Fehlermeldung #ZAHL! fällig. Probieren Sie es einfach einmal aus … In der Mustertabelle ist das gut nachzuvollziehen.
Die erste Formel, die wirklich funktioniert, sehen Sie in Zeile 5. Egal, ob dieses Jahr der Geburtstag schon war, heute ist oder noch folgt, das Ergebnis stimmt. Die Formel ist so gestaltet, dass ein Jahr dazu addiert wird, wenn der Geburtstag in diesem Jahr bereits gewesen ist. Das wird mit einer WENN()-Funktion realisiert. Dadurch wird die Formel zwar etwas länger aber ist für viele User etwas transparenter, übersichtlicher. Dieser Teil der Formel ist dafür verantwortlich:
+WENN(DATUM(JAHR(HEUTE());MONAT(GebTag);TAG(GebTag))<HEUTE();1;0)
Dort wird Monat und Tag des Geburtstages in das aktuelle Jahr transferiert und dann verglichen, ob dieses generierte Datum kleiner ist, als der heutige Tag. Wenn das der Fall ist, dann wird der Geburtstag des nächsten Jahres zur Berechnung verwendet.
Details dazu: -> überlesen
Der Teil der Formel beginnt mit einem Plus. Das bedeutet, es wird etwas (zur Jahreszahl) addiert. Dann folgt die WENN-Bedingung. Trifft diese zu, dann wird der Wert 1 addiert, sonst 0. Die Bedingung selbst ist wiederum eine verschachtelte Funktion. Mit DATUM() wird ein Excel-Datum aus den drei Komponenten JAHR(), MONAT() und TAG() zusammen gesetzt.
- JAHR(HEUTE()) ist naturgemäß immer das aktuelle Jahr.
- MONAT(GebTag) gibt den Monat des Datumswertes zurück, der in der Zelle mit dem Bereichsnamen GebTag steht. Sie werden vielleicht in vielen Fällen die tatsächliche Adresse, hier B3 eingeben. Dennoch hat der Bereichsname seinen Charme, er ist „sprechend”.
- TAG(GebTag) ist praktisch das Gleiche wie vor, nur wird der Tag zurück gegeben.
Mit dieser Funktion und den drei Argumenten ist ein Excel-Datum berechnet worden, welches dem Geburtstag im aktuellen Jahr entspricht. Mit anderen Worten und stark vereinfacht sagt die Formel nun folgendes aus:
+ WENN(GeburtstagDiesesJahr < HEUTE();1;0)
Also: Wenn der Geburtstag dieses Jahres in der Vergangenheit liegt (weil kleiner als Heute), dann Plus_1, sonst Plus_0. – Und da dieser Teil der Formel ja wiederum ein Teil einer anderen Formel ist, wird nun ‑ausgehend vom aktuellen Datum- berechnet, welches der nächste Geburtstag ist.
Zugegeben, das Ganze ist nicht wirklich leicht. Aber wenn Sie Schritt für Schritt vorangehen, die Formel in Blöcken analysieren wie eben für einen Teil geschehen und das auch mehrfach wiederholen, dann werden Sie auch verstehen, wie das Ganze zustande kommt.
Vom Prinzip her gleich aber ohne die WENN()-Funktion arbeitet die Formel in Zeile 6. Dort wird per Logik ausgewertet, ob der Geburtstag schon gewesen ist oder nicht:
+(DATUM(JAHR(HEUTE());MONAT(GebTag);TAG(GebTag))<HEUTE())
Dort wird „behauptet”, dass der diesjährige Geburtstag kleiner ist als der heutige Tag. Das ist entweder WAHR oder FALSCH. Da intern WAHR als 1 und FALSCH als 0 gewertet wird, addiert Excel den entsprechenden numerischen Wert. Für Excel-Freaks ist das gewiss klarer und „besser”, aber nicht für jeden sofort einsehbar.
Last but not least wird die eben dargelegte Formel in Zeile 7 so umgestellt, dass die DateDif()-Funktion zum Einsatz kommt. Dadurch kann die Variabilität etwas erhöht werden. Das ist aber Geschmackssache.
Natürlich lässt sich das Ganze auch mit VBA (also per Makro) berechnen, dazu fügen Sie den folgenden Code in ein Modul dieser Mappe ein:
Option Explicit
Function RestTageGebTag(GebTag As Date) As Integer
Dim GT As Date
GT = DateSerial(Year(Date), Month(GebTag), Day(GebTag))
If GT < Date Then GT = DateSerial(Year(Date) + 1, _
Month(GebTag), Day(GebTag))
RestTageGebTag = GT - Date
End Function
Auch hier ist es naturgemäß wichtig, dass die Zelle mit dem Geburtstag ein korrektes Datum enthält. Diese Funktion (UDF) ist nicht in der Datei enthalten. Der Aufruf erfolgt so: =RestTageGebTag(B2) und als Ergebnis wird die Anzahl der Tage bis zum nächsten Geburtstag zurück gegeben.
Noch einmal zurück zu den reinen Excel-Funktionen ohne VBA. Schauen Sie sich einfach einmal die Zeilen 9 :11 an. Es ist gewiss nicht leicht, hinter das Geheimnis zu kommen, warum das läuft.
Ich beginne einmal mit der Funktion EDATUM(). Damit wird eine als Argument übergebene Anzahl von Monaten zum Ausgangsdatum hinzu addiert. Soweit, so gut. Es muss nur noch irgendwie festgestellt werden, ob der Geburtstag im laufenden Jahr schon war, heute ist oder noch kommt. Im erstgenannten Fall müssen 12 Monate zum Tag der Geburt dieses Jahres hinzu gerechnet werden, dann lässt sich die korrekte Differenz berechnen.
So weit, so klar. Bleibt das unbekannte GebTagDJ. Das ist meine Abkürzung für „Geburtstag Dieses Jahr” und ist ein berechneter Wert. Dieser Name steht stellvertretend für den berechneten Geburtstag im aktuellen Jahr. Geben Sie einfach in eine Zelle =GebTagDJ ein und formatieren Sie die Zelle als Datum.
Und woher „weiß” Excel diesen Wert? Es kann sich ja nicht um eine Funktion handeln, denn es fehlen ja die für eine Funktion typischen Klammern(). Zugegeben, etwas tricky gemacht. Ich habe über den Namensmanager den Namen GebTagDJ deklariert und auch gleich einen passenden Wert berechnen lassen. Der Aufwand lohnt gewiss nicht für eine einzige Formel, aber wenn ich so etwas in der Mappe öfter brauche oder eine Berechnung etwas verschleiern will, dann ist das ganz hilfreich.
Gleichermaßen habe ich den Namen GenJ erstellt und einen Wert zugewiesen. Es wird ein logischer Wert berechnet, ob Geburtstag erst nächstes Jahr ist. Und wenn ich WAHR als Multiplikator verwende, dann rechnet Excel mit 1, FALSCH entspricht 0. In Zeile 11 wäre das dann so, dass entweder 12*1 (also 12) Monate dazu addiert werden, falls der Geburtstag schon war oder 12*0 also kein Monat dazu addiert wird, wenn der Geburtstag heute ist oder dieses Jahr noch kommt.
[NachObenLetzte Verweis=„ML: noch ?? Tage bis …”]