Excel, ein aktuelles Kuendigungsdatum eines Vertrages?
Hallo in die Runde.
Ich habe eine Tabelle mit Verträgen.
Eine Zelle mit dem heutigen Datum als separater Wert.
Weiter das Anfangsdatum, die Laufzeit und die Kuendigungsfrist.
Wenn ich die Tabelle öffne, möchte ich in einer separaten Zelle jeweils das Datum haben, zu welchem Tag die nächste Kündigung möglich ist.
Ich hoffe, jemand kann helfen.
4 Antworten
Hi,
Kannst Du bitte den Aufbau Deiner Tabelle etwas genauer beschreiben und insbesondere angeben, in welcher Einheit Du die Vertragslaufzeit und die Kündigungsfrist abgelegt hast? Sind das Monate?
Und dann noch eine Verständnisfrage: Willst Du den Termin wissen, an dem die Kündigung wirksam wird, wie oben geschrieben? Dazu bräuchtest Du eigentlich die Kündigungsfrist nicht. Oder doch eher das Datum, bis zu dem die Kündigung zum nächstmöglichen Termin ausgesprochen werden muss?
Gruß
Hannes
Hallo Hannes, zunächst vielen Dank Dir. Ich würde gern einen Auszug aus der Excel Tabelle hier herein kopieren. Geht leider nicht. Die Überschriften der Spalten wie folgt chronologisch nebeneinander von links nach rechts:
- Vertragsbeginn des Vertrages, Datum (31.08.2012)
- Grundlaufzeit des Vertrages, Monate (12)
- Vertragsende regulär, Datum (30.08.2013)
- Verlängerung des Vertrages wenn nicht gekündigt wird, jeweils immer um Monate (12)
- Vertragsende real, Datum (muss ausgerechnet werden)
- Kündigungsfrist, Monate (3)
- spätestens zu kündigen am (muss ausgerechnet werden)
(über dieser letzten Spalte steht dann separat das heutige Datum, welches sich jeweils aktualisiert)
D.h. ich möchte in der letzten Spalte immer am jeweils gültigen aktuellen Datum wissen, wann ich den Vertrag kündigen muss.
Hallo Rabu,
So sollte das gehen:
A2: Startdatum
B2: Erste Laufzeit in Monaten
C2: =EDATUM(A2;B2)-1
(nach meinem Verständnis endet ein Vertrag, der am 2. eines Monats beginnt, am 1. der Endmonats.
D2: Dauer der automatischen Vertragsverlängerung in Monaten
E2: =EDATUM(A2;B2+SUMMENPRODUKT(((EDATUM(A2;B2+(ZEILE($A$1:$A$40)-1)*D2-F2))<=HEUTE())*1)*D2)-1
F2: Kündigungsfrist in Monaten
G2: =EDATUM(E2;-F2)
Bis zu diesem Datum muss die Kündigung eingegangen sein, um zum nächstmöglichen Kündigungstermin wirksam zu werden.
Ob Du das aktuelle Datum durch die Formel =heute() noch irgendwo einfügst, kannst Du selber entscheiden. Als separate Zelle wird es nicht gebraucht.
Die Formeln kannst Du einfach runterziehen. Das Ergebnis sieht dann so aus:
Ich hoffe, das hilft Dir und freue mich auf Feedback.
Gruß
Hannes
Du hast das Argument der Zeile-Funktion verändert. Dadurch wird das Enddatum falsch berechnet.
Statt
ZEILE($A$2:$A$10)
sollte es heißen
ZEILE($A$1:$A$40)
Diese Funktion steuert, dass 40 Vertragsperioden durchgerechnet werden, um den ersten erreichbaren Vertragsendtermin zu bestimmen, der später als heute()+Kündigungsfrist liegt. Du kannst die 40 reduzieren, musst aber bei 1 beginnen.
Hast Du die Formeln denn nicht kopiert?
Gruß
Hannes
Nein hatte ich nicht kopiert. Ich probiere morgen auf Arbeit. Schönen Abend erst einmal und Danke
Hallo Rabu,
die 40 ist die maximale Anzahl an automatischen Vertragsverlängerungen. Für einen Vertrag, den Du 1980 abgeschlossen hast und der sich seither jährlich verlängert hat, würde immer noch das richtige Kündigungsdatum berechnet. Bei Bedarf kann die Zahl hochgesetzt werden. Ich fand 40 schon recht großzügig.
Wenn Dir meine Lösung geholfen hat, würde ich mich über eine positive Bewertung freuen.
Gruß
Hannes
Vielen Dank Dir. Es klappt alles, aber mit den 40 Varianten - da wäre ich nie drauf gekommen. Danke nochmal
Moin,
wenn die Frist und Laufzeit in Tagen sind, kannst du folgende Form nutzen
=Anfangsdatum+Laufzeit-Kündigungsfrist [jeweils auf die Zellen beziehen]
Ansonsten bietet sich =DATUM(Jahr;Monat;Tag) an, mit dem du das Datum zusammensetzen kannst, da Laufzeit und Frist wohl in Monaten angegeben sind(?)
=DATUM(JAHR(Anfangsdatum);MONAT(Anfangsdatum)+Laufzeit-Frist;TAG(Anfangsdatum))
Bersten Dank für deine Hilfe. Ich probiere das aus! Schönes Wochenende!
Sind dies befristete Verträge ?
Es gibt häufig auch die Angabe:
3 Monate "zum Quartalsende"
Sofern dies vorkommt wird es etwas kniffliger.
Oder die üblichen Handyverträge. Zunächst ist die Laufzeit 2 Jahre, dann Verlängerung jeweils um 1 Jahr, sofern nicht 3 Monate vor Vertragsende gekündigt wird.
Nirahimsas Formel berücksichtigt diese automatischen Verlängerungen nicht und würde ggfs auch ein Kündigungsdatum liefern, das und der Vergangenheit liegt.
Bin gespannt, ob wir noch ein paar mehr Infos bekommen, oder ob sich die Anfrage erledigt hat.
Vielen Dank Hannes, hat geklappt, bis auf eine Zeile. Ich habe die Formel genau wie Du es vorgegeben hast in E2 eingegeben. Ich hoffe, ich habe mich nicht verschrieben.
=EDATUM(A2;B2+SUMMENPRODUKT(((EDATUM(A2;B2+(ZEILE($A$2:$A$10)-1)*D2-F2))<=HEUTE())*1)*D2)-1
Bei der Spalte E und G bringt es mir bei "einem" Vertragsdatum das Vertragsende vom letzten Jahr.
Vertragsbeginn 31.08.12
Grundlaufzeit 12 Monate
Vertragsende regulär, 30.08.2013
Verlängerung 12 Monate
Vertragsende real hier: 30.08.2017
Kündigungsfrist 1 Monat
spätestens zu kündigen: 30.07.2017
Es kann eigentlich nur ein Tippfehler sein, weil die Formel bei allen anderen Zellen funktioniert.
Ich weiß leider nicht, wie eine Excel Tabelle hier hereinkopiert wird...