Endtermine exakt berechnen: Vertragscontrolling mit EDATUM

Vorgestern wurde ich im Excel-Kurs in Berlin gefragt, wie sich ein Vertragsende berechnen lässt, wenn folgende Regeln gelten:

  • Das Vertragsende liegt genau 2 Jahre minus 1 Tag nach Vertragsbeginn – beim Beginn 15.06.2012 wäre das der 14.06.2014.
  • Fällt das Vertragsende auf ein Wochenende, muss der Termin auf den Freitag davor datiert werden.

Die Funktion EDATUM ist hier der Schlüssel zur Lösung. Sie wird kombiniert mit WOCHENTAG und WENN.

So wird die Lösung aufgebaut

Mit EDATUM werden Fälligkeitstermine berechnet. EDATUM liefert den Termin, der eine bestimmte Anzahl von Monaten vor bzw. nach einem angegebenen Datum liegt – hier also nach einem Vertragsbeginn.

Mit der Funktion WOCHENTAG lässt sich überwachen, ob der mit EDATUM ermittelte Termin auf ein Wochenende fällt. Dazu wird WOCHENTAG um das Argument 2 ergänzt und liefert so die Ziffer 6 für Samstag und 7 für Sonntag.

Beide Funktionen arbeiten innerhalb einer verschachtelten WENN-Funktion.

Vertragsende präzise nach Vorgabe berechnet

Bild 1: Berechnetes Vertragsende-Datum unter Berücksichtigung von Wochenenden

In der Vertragstabelle ermittelt folgende Formel in B5 das korrekte Vertragsende:

=WENN(WOCHENTAG(EDATUM(A5;24);2) = 6; EDATUM(A5;24) – 2; WENN(WOCHENTAG(EDATUM(A5;24);2) = 7; EDATUM(A5;24) – 3;EDATUM(A5;24) – 1))

Zur Erläuterung:

  • EDATUM(A5;24) – 1 ermittelt vom Vertragsbeginn-Datum in A5 ein exakt 24 Monate später liegendes Datum. Laut Vorgabe soll das Vertragsende einen Tag vor diesem Datum liegen; deshalb also minus 1.
  • WOCHENTAG(EDATUM(A5;24);2) = 6; EDATUM(A5;24) – 2 zieht – für den Fall, dass das berechnete Datum auf einen Samstag fällt, einen zusätzlichen Tag ab; deshalb minus 2.
  • WOCHENTAG(EDATUM(A5;24);2) = 7; EDATUM(A5;24) – 3 zieht dann zwei zusätzliche Tage ab, wenn der berechnete Tag ein Sonntag ist; deshalb minus 3.

Auch unterschiedliche Vertragslaufzeiten berücksichtigen

Doch was ist, wenn nicht alle Verträge die gleiche die Laufzeit von 24 Monaten haben und beispielsweise über ein Jahr oder auch nur ein paar Monate laufen?

Flexibler ist da eine Lösung, die neben den beiden Spalten für Vertragsbeginn und -ende auch noch eine Spalte für die Vertragslaufzeit (in Monaten) enthält. Die folgende Abbildung zeigt eine solche Lösung.

Vertragslaufzeit wird bei der Berechnung des Vertragsendes flexibel berücksichtigt

Bild 2: Vertragslaufzeit wird bei der Berechnung des Vertragsendes flexibel berücksichtigt

Die Formel in der Spalte Vertragsende lautet in dem Fall für Zelle C5:

=WENN(WOCHENTAG(EDATUM(A5;B5)-1;2) = 6; EDATUM(A5;B5) -2; WENN(WOCHENTAG(EDATUM(A5;B5)-1;2) = 7;EDATUM(A5;B5) -3;EDATUM(A5;B5) -1))

Hier wird die feste Vertragslaufzeit von 24 Monaten ersetzt durch die in Spalte B hinterlegte Laufzeit. Die Angabe Monat(e) wurde in dieser Zelle als benutzerdefiniertes Format 0 „Monat(e)“ festgelegt.

Alternative Formel bei intelligenter Tabelle

Wird die Tabelle in eine so genannte intelligente Tabelle umgewandelt (über den Befehl Als Tabelle formatieren in der Registerkarte Start ), dann ist die Formel dank der strukturierten Verweise leichter verständlich:

=WENN(WOCHENTAG(EDATUM([@Vertragsbeginn];[@Vertragslaufzeit])-1;2) = 6; EDATUM([@Vertragsbeginn];[@Vertragslaufzeit])-2; WENN(WOCHENTAG(EDATUM([@Vertragsbeginn];[@Vertragslaufzeit])-1;2)=7;EDATUM([@Vertragsbeginn];[@Vertragslaufzeit])-3;EDATUM([@Vertragsbeginn];[@Vertragslaufzeit])-1))

Dieser Beitrag wurde unter Excel abgelegt und mit , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Eine Antwort auf Endtermine exakt berechnen: Vertragscontrolling mit EDATUM

  1. Sehr geehrte Damen und Herren,
    vielen Dank für diese Übersicht. Sie hat mir bei einfachen Übersichten sehr geholfen.
    Nun haben wir in unserer Firma auch Verträge, die nicht nur eine Laufzeit, sondern auch eine automatische Verlängerung der Laufzeit und eine Kündigungsfrist enthalten haben.
    1) Würde ich gerne die Vertragslaufzeit mit der autom. Verlängerung in 1 Zelle darstellen und
    2) das späteste Kündigungsdatum in einer weiteren Zelle.
    Können Sie hier weiterhelfen?

    Mit freundlichen Grüßen,

    Liza Knezlova

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.