Excel-Tutorial: Berechnen und Hervorheben von Ablaufdaten

Inhaltsverzeichnis

Verwandte Verknüpfungen

In diesem Video sehen wir uns an, wie Ablaufdaten berechnet und hervorgehoben werden.

Angenommen, Ihr Unternehmen hat ein Mitgliedschaftsprogramm gestartet und Ihr Chef hat Ihnen gerade einen Datensatz gesendet. Sie hat Ihnen eine Liste von 1000 Personen gegeben, die im letzten Jahr oder so eine Mitgliedschaft erneuert haben, und sie sucht nach verschiedenen Dingen.

Zunächst möchte sie, dass Sie ein Ablaufdatum für ein Jahr in der Zukunft berechnen, am letzten Tag des gleichen Monats, in dem die Mitgliedschaft erneuert wurde.

Zweitens möchte sie sehen, wie viele Tage noch bis zum Ablauf verbleiben.

Drittens möchte sie den Status "Abgelaufen" für bereits abgelaufene Mitgliedschaften und "Bald ablaufen" für alle Mitgliedschaften sehen, die in den nächsten 30 Tagen ablaufen.

Schließlich sagte sie, es wäre schön, abgelaufene Mitglieder in Pink und diejenigen, die bald ablaufen, in Gelb hervorzuheben.

Auch - kann sie es vor ihrem Mittagessen am Mittag haben?

Ähm, sicher.

Zuerst konvertieren wir diese Daten in eine richtige Excel-Tabelle. Dies erleichtert die Eingabe von Formeln erheblich, da Excel sie im Laufe der Zeit automatisch nach unten kopiert.

Berechnen wir nun die Ablaufdaten. Diese sollen ein Jahr später Ende desselben Monats sein, aber lasst uns zuerst einen einfachen Hack verwenden, um in den Ballpark zu gelangen. Wie Sie in früheren Videos gesehen haben, sind Datumsangaben nur Seriennummern, sodass wir einfach eine Formel eingeben können, die dem erneuerten Datum 365 Tage hinzufügt.

Dies ist ein guter Anfang. Wir können die Lösung fertig schruppen und später darauf zurückkommen, um dies zu beheben.

Wenn Sie ein komplexeres Problem in Excel lösen, ist es eine gute Idee, den Gesamtansatz zu validieren und am Ende auf die Details zurückzukommen. Sie möchten nicht gleich zu Beginn an einer kleinen Sache hängen bleiben, insbesondere wenn sich der Ansatz ändern kann.

Nachdem wir ein Ablaufdatum haben, können wir die verbleibenden Tage berechnen. Dies muss in Zukunft automatisch aktualisiert werden, daher verwenden wir die TODAY-Funktion, die immer das heutige Datum zurückgibt.

Die Formel lautet einfach E5 minus HEUTE (). Wenn ich auf "Zurück" drücke, verbleiben noch Tage vor Ablauf. Negative Zahlen zeigen an, dass eine Mitgliedschaft bereits abgelaufen ist.

Für den Status verwenden wir eine einfache verschachtelte IF-Formel. Wenn die verbleibenden Tage kleiner als Null sind, ist die Mitgliedschaft abgelaufen. Andernfalls sollte der Status "Bald ablaufen" lauten, wenn die verbleibenden Tage weniger als 30 betragen. Ansonsten ist der Status nichts.

=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))

Als nächstes müssen wir bedingte Formatierungsregeln erstellen, die diese Werte hervorheben.

Wählen Sie zuerst die Daten aus und setzen Sie die aktive Zelle in die obere rechte Ecke. Erstellen Sie dann eine Formelregel, die die aktive Zelle auf den Wert "Abgelaufen" testet. Die Spalte muss gesperrt sein.

Wiederholen Sie nun denselben Vorgang für Mitgliedschaften, die "bald ablaufen".

Das sieht gut aus. Wir müssen nur die Ablaufdaten festlegen, um am letzten Tag des Monats zu landen.

Nun, es stellt sich heraus, dass es eine coole Funktion namens EOMONTH (für Monatsende) gibt, die den letzten Tag eines Monats in der Vergangenheit oder Zukunft erhält.

Das Startdatum ist das erneuerte Datum und die Monate sind 12.

Und da haben Sie es, alles, was Ihr Chef wollte, und Sie haben noch Zeit für eine Tasse Kaffee vor dem Mittagessen.