Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wenn Enddatum erreicht Jahreszahl +1

Wenn Enddatum erreicht Jahreszahl +1
Thomas
Hallo Excelfreund,
ich habe eine Tabelle in der ich Verträge aufgelistet habe mit Anfangsdatum (ab C6) und Enddatum (ab D6). In I6 trage ich ein ob der Vertrag gekündigt ist oder nicht (ist/ ist nicht). In B1=Heute()
Nun verlängern sich die Vertäge nach Ablauf immer um ein weiteres Jahr wenn nicht gekündigt wird und dies soll automatisch nun in D6 geschehen wenn in I6 "ist nicht" steht.
ZB: Anfang 01.01.2010 und das Enddatum (D6) soll dann wenn in I6 "ist nicht" steht so lauten 01.01.2011 oder
Anfang 01.01.2000 und das Enddatum dann 01.01.2011 da wir ja 2010 haben.
Noch etwas, das Enddatum wird mir als Meldung in Outlook gezeigt das ich die Kündigungsfrist nicht verpasse und darum sollte sich die Ablaufzeit immer automatisch um ein Jahr verlängern wenn "ist nicht" in I6 steht.
Mit Datum hab ichs nicht so und darum wende ich mich an euch. Besten Dank schon im voraus.
Mit freundlichen Grüßen
Thomas T. aus S

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Wenn Enddatum erreicht Jahreszahl +1
24.07.2010 21:15:12
JogyB
Hallo Thomas,
sehe keine Möglichkeit, das über eine Formel zu erledigen. Was gehen würde wäre die automatische Verlängerung, weil man ja davon ausgehen kann, dass das Ablaufdatum laufender Verträge zwischen dem heutigen Tag und Heute in einem Jahr liegen muss. Was jedoch nicht geht, ist dass bei gekündigten Verträgen keine Neuberechnung der Zelle erfolgt (zumindest kenne ich keine Möglichkeit).
Mit VBA wäre es einfach, ist das für Dich ein Option?
Gruß, Jogy
AW: Wenn Enddatum erreicht Jahreszahl +1
24.07.2010 21:29:53
Thomas
Hallo Jogy,
alles was zum gewünschten Erbebnis führt ist für mich eine Option.
Mein gewünschtes Ergebnis ist eine kompi zwischen Formel und Bedingung das auch über VBA mir recht ist.
MfG. Thomas T. aus S
Anzeige
so in etwa?
25.07.2010 02:39:45
silex1
Hallo,
Arbeitsblatt mit dem Namen 'Tabelle3'
 CDHI
5BeginnAblauf  
611.11.199611.11.2011 ist nicht

ZelleFormel
D6=DATUM(WENN(I6="ist nicht";JAHR(HEUTE())+1;JAHR(HEUTE()));MONAT(C6);TAG(C6))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

VG, Rene
Anzeige
AW: so in etwa?
25.07.2010 10:02:11
Thomas
Hallo Rene,
genau das wars danke nun kann ich mich daran machen das ich nicht 3,5 Monate lang die Meldung bekomme das der Vertrag ausläuft. Mit deiner Formel sind alle Meldungen die in der Vergangenheit sind nun weg.
Dank dir nochmal Rene und wünsch Dir ein schönes We.
Gruß Thomas T.
AW: so in etwa?
25.07.2010 11:25:58
JogyB
Ähm, sorry, aber wenn es das ist, was Du gebraucht hast, dann passt es nicht zu Deiner Beschreibung. Diese Formel setzt den Vertragsablauf bei "ist nicht" immer auf das Folgejahr, d.h. ein Vertrag der am 31.12. abgeschlossen wurde läuft damit
- wenn er ungekündigt ist am 31.12.2011 aus - anstatt dem korrekten Datum 31.12.2010
- wenn er gekündigt ist am 31.12.2010 - und das unabhängig davon, wann er gekündigt wurde
Wenn es Dir nur darum geht, dass das Ablaufdatum eines gekündigten Vertrages in der Vergangenheit liegt, dann kannst Du die Formel nehmen:
=DATUM(WENN(I6="ist nicht";WENN(DATUM(JAHR(HEUTE());MONAT(C6);TAG(C6))<HEUTE();JAHR(HEUTE())+1; MAX(JAHR(HEUTE());JAHR(C6)+1));JAHR(HEUTE())-1);MONAT(C6);TAG(C6))
Das MAX stellt nur sicher, dass wenn Du einen Vertrag einträgst, der erst in der Zukunft beginnt, dieser auch mit dem Folgejahr als Ablaufdatum eingetragen wird.
Wenn Du statt "ist" ein Kündigungsdatum einträgst, könnte man das noch besser machen.
Wenn Du aber bei "ist" und "ist nicht" bleiben willst und trotzdem das korrekte Kündigungsdatum haben willst, dann bleibt nur eine VBA-Lösung.
Gruß, Jogy
Anzeige
...und was ist mit Kündigungsfristen? ;-) owT
25.07.2010 12:13:21
silex1
VG, Rene
AW: so in etwa?
25.07.2010 12:33:19
Thomas
Hallo Jogy,
also wenn ich den Vertrag am 31.12.2010 abschließe so soll das Vertragsende der 31.12.2011 sein.
Kündige ich den Vertrag Fristgerecht oder gleich nach beginn so wird sich das Enddatum dann nicht mehr verändern wenn das Datum erreicht ist. Das "ist nicht" oder "ist" wird an Outlook übergeben als Text:Hallo Thomas, der Vertrag "B6" läuft am "D6" aus, und der Vertrag "I6" (ist/ ist nicht) gekündigt.
In Spalte B stehen die Verträge, in D das Enddatum in abhänigkeit von I. Genau lautet der Text dann, Hallo Thomas, der Vertrag Autoversicherung läuft am 01.01.2011 aus, und der Vertrag ist gekündigt.
Gruß Thomas T.
Anzeige
AW: so in etwa?
25.07.2010 14:05:43
JogyB
Hallo Thomas,
probier das mal aus:
Sub pruefeVertraege()
Const begCol = 3 ' Spalte, in der der Vertragsbegin steht
Const endCol = 4 ' Spalte, in der das Vertragsende steht
Const istCol = 9 ' Spalte, in der ist/ist nicht steht
Const firstRow = 6 ' erste Zeile, in der Daten stehen
Const sheetNr = 1 ' Nr. oder Name des Worksheets, auf dem die Daten stehen
Dim zeiLe As Long
Dim fehLerSammler As String
Dim letzteZeile As Long
On Error GoTo errorHandler
Application.ScreenUpdating = False
With ThisWorkbook.Sheets(1)
' letzteZeile bestimmen
letzteZeile = .Cells(Rows.Count, begCol).End(xlUp).Row
' Über alle Zeilen mit Daten
For zeiLe = firstRow To letzteZeile
' Wenn "ist nicht" in Spalte J
If .Cells(zeiLe, istCol).Text = "ist nicht" Then
' Prüfen, ob Datum in Spalten C und D
If IsDate(.Cells(zeiLe, begCol)) And IsDate(.Cells(zeiLe, endCol)) Then
With .Cells(zeiLe, endCol)
' Wenn Enddatum in der Vergangenheit, dann um ein Jahr erhöhen
' und zwar bis das Enddatum größer als der heutige Tag ist
' (zur Sicherheit, falls das Datum von Hand falsch eingetragen wurde)
While CDate(.Value)  "" Then
MsgBox ("Fehler in folgenden Spalten: " & fehLerSammler)
End If
Application.ScreenUpdating = True
Exit Sub
errorHandler:
' Wenn Fehler vor der For-Schleife
If zeiLe = 0 Then
MsgBox ("Fehler beim Bestimmen der letzten Zeile")
' Wenn Fehler in der For-Schleife
' dann ist zeile 

Das kommt in ein eigenes Modul. Du kannst das dann in Workbook_Open mit mit "Call pruefeVertraege" aufrufen, dann wird bei jedem Öffnen der Datei aktualisiert. Oder Du rufst es im Code der Tabelle unter Worksheet_Activate auf, dann ist die Aktualisierung jedesmal, wenn Du auf das Blatt gehst bzw. setz es unter Worksheet_Calculate, dann aktualisiert es bei jeder Neuberechnung des Blattes.
Gruß, Jogy
Anzeige
AW: so in etwa?
25.07.2010 17:25:59
Thomas
Hallo Jogy,
Noch etwas, das Enddatum wird mir als Meldung in Outlook gezeigt das ich die Kündigungsfrist nicht verpasse.
Das sagte ich schon in meinem ersten Hilferuf. Ich wollte nur Hilfe bei Wenn Enddatum erreicht Jahreszahl +1 und ob das über Formel oder VBA erreicht wird ist mir egal. Die Tabelle hab ich hier aus dem Forum und diese hab ich bis auf die Formel ohne hilfe anpassen können obwohl ich es auch nicht so mit VBA hab.
Sorry das Du dir umsonst mühe gemacht hast.
Gruß Thomas T.
AW: so in etwa?
25.07.2010 18:22:58
JogyB
Hallo Thomas,
genau dieses "Wenn Enddatum erreicht Jahreszahl +1" geht mit einer Formel nicht (zumindest nicht mit Deiner Tabellenstruktur). Keine der hier genannten Formeln kann das, die von silex1 macht überhaupt nicht das was Du willst und die von mir macht es nur bedingt (wenn Vertragsende wirklich immer Vertragsbeginn + x Jahre), außerdem ändern beide Formeln auch bei "ist" zwangsläufig das Vertragsende.
Gruß, Jogy
Anzeige
AW: so in etwa?
25.07.2010 19:57:10
Thomas
Hallo Jogy,
hier die Tabelle von der ich ausgegangen bin. In Spalte Outlook ja/nein werden mit ja dann in Outlook an den Kaleder die Einträge aus der Tabelle übegeben.
Gruß Thomas T.
Tabelle1
 BCDEFGHI
5VertragVertragsbeginn
[Datum]
Vertragslaufzeit
[Datum]
Kündigungsfrist
[Wochen]
Outlook
[ja/ nein]
Vorlaufzeit
[Wochen]
Outlookeintrag
[Wochen]
gekündigt [ist/ ist nicht]
6Handy Thomas04.08.200704.08.201012nein214ist
7Handy Carola29.01.200829.01.201112nein214ist nicht
8               
9               
10               
11               
12               
13               
14               

verwendete Formeln
Zelle Formel Bereich
D6 =DATUM(WENN(I6="ist nicht";JAHR(HEUTE())+1;JAHR(HEUTE()));MONAT(C6);TAG(C6)) 
H6 =+E6+G6 
D7 =DATUM(WENN(I7="ist nicht";JAHR(HEUTE())+1;JAHR(HEUTE()));MONAT(C7);TAG(C7)) 
H7 =+E7+G7 

Tabellendarstellung in Foren Version 5.29


Anzeige
AW: so in etwa?
25.07.2010 21:55:55
JogyB
Hallo Thomas,
und jetzt mach mal eine Zeile mit Vertragsbeginn 31.12.1999 und "ist nicht". Da sollte dann doch als Vertragsende der 31.12.2010 drinstehen, mit der Formel kommt aber der 31.12.2011. Und jetzt mach ein "ist" daraus und denk Dich zum 1.1.2011 vor... dann steht da nämlich wieder der 31.12.2011, obwohl der Vertrag gekündigt und zum 31.12.2010 ausgelaufen ist. Und am 1.1.2012 steht dann plötzlich 30.12.2012 drin.
Und jetzt denke noch an einen Altvertrag, der schon vor fünf Jahren gekündigt wurde. Der bekommt (sowohl mit meiner als auch Renes Formel) ein Datum, dass dieses Jahr ±1 ist.
Und das ist wirklich, was Du haben willst?
Mein Makro erhöht bei "ist nicht" das Vertragsende um ein Jahr und bei "ist" lässt es das Datum unangetastet. Damit funktioniert alles wunderbar, solange Du die Einträge in Spalte I sauber pflegst. Wenn Du allerdings mal was vergessen hast, dann können falsche Einträge entstehen.
Deswegen wäre es am Sinnvollsten, eine Spalte mit dem Kündigungsdatum einzuführen, dann könnte man das sauber mit Formeln erledigen (auch unter Berücksichtigung der Kündigungsfrist). Der Eintrag "ist" und "ist nicht" ergibt sich dann automatisch.
Gruß, Jogy
Anzeige
@JogyB
25.07.2010 21:10:49
silex1
Hallo Jogy,
weder meine noch Deine Formel, noch Dein Code machen das, was Du gedenkst.
1.) gibt es nirgens eine Datumsangabe, wann gekündigt wurde!
2.) gibt es zig Kündigungsfristen und Möglichkeiten, die hier nicht beachtet werden und
3.) ist mit meiner Formel dem Wunsch von Thomas entsprochen, denn er selbst reguliert den Eintrag in Outlook mittels Spalte F.
Ach und
4.) ich hab keine Lust um Grundsatzdebatten abzuhalten ob was geht und wie und so werde ich nur "stiller Beobachter" dieses Beitrags bleiben.
VG, Rene
AW: @JogyB
25.07.2010 21:44:38
JogyB
Hallo Rene,
zu Punkt 1): Solange er seine Liste sauber pflegt, macht der Code genau das, was er will
zu Punkt 2): und die kann er mit seinem Eintrag in Spalte F wunderbar steuern
zu Punkt 3): nein, tut sie nicht. Und wenn Du sie mal getestet hättest, dann wüsstest Du das auch. Im Endeffekt hättest Du nur Dein eigenes Beispiel anschauen müssen. Da sollte nämlich nicht 11.11.2011 sondern 11.11.2010 stehen.
Und ich führe hier keine Grundsatzdebatten, sondern versuche die Aufgabenstellung des TO zu lösen. Deine Formel tut das nicht mal ansatzweise, aber anscheinend ist es Dir wichtiger, wegen meines Hinweises darauf beleidigt zu sein als nach einer Lösung zu suchen.
Gruß, Jogy
Anzeige
AW: @JogyB
26.07.2010 05:31:15
Thomas
Guten Morgen Ihr Zwei,
ich hatte nicht vor die Tabelle als Archiv zu führen sondern wenn die Verträge ausgelaufen sind durch aktuelle zu ersetzen under dann zu Löschen. Also nur eine Zusammenfassung meiner Verträge die dann im Outlookkalender mir die Kündigungsfrist +Voraufzeit wieder gibt und das tuts. Zudem Frage noch offen? Nein
Gruß Thomas T.
AW: @JogyB
26.07.2010 09:06:47
JogyB
Hallo Thomas,
es ist für Dich also korrekt, dass ein am 31.12.2009 abgeschlossener Vertrag mit einjähriger Laufzeit den 31.12.2011 als Vertragsende hat?
Du hast zwar von mir eine Formel und ein Makro bekommen, die zumindest das korrekt machen würden, aber wenn Du darauf bestehst eine Formel zu verwenden, die offensichtlich falsche Ergebnisse liefert, dann kann ich Dir auch nicht helfen.
Gruß, Jogy
AW: @JogyB
26.07.2010 17:03:39
Thomas
Hallo Jogy,
also bei den Anfangsdatum und Enddatum wo ich eingetragen hatte (derzeit 9 st.) hat das wunderbar funktioniert aber ich Speicher mir so oder so immer meine Fragen ab um immer auf die Lösungen zurückgreifen kann. Ich werde das mit deinem Datum mal versuchen und auch mit anderen. Werd also mal die Tabelle stressen.
Gruß Thomas T.
AW: @JogyB
26.07.2010 17:27:42
Thomas
Hallo Jogy,
so es stimmt beim 31.12. hengt sich das Formelergebnis auf aber eine Autoversicherung endet oder fängt am 01.01. um 12 Uhr an. Klar gibts auch Verträge die ab Vertragsdatum an 1 Jahr laufen. Das heißt für mich das ich kein Vertrag am 31.12. abschließe oder ich nehm deine Lösung.
Gruß Thomas T.
AW: @JogyB
26.07.2010 18:45:36
JogyB
Hallo Thomas,
nimm einfach meine Formellösung, die fängt das ab.
Gruß, Jogy
AW: @JogyB
26.07.2010 18:58:37
Thomas
Hallo Jogy,
was aber auch gut zu wissen wäre wie sich die Formel am Jahreswechsel dann verhält, also ob das nur eine vorrübergehende Erscheinung ist.
Aber gut ich änder das.
Ich dank euch beiden Gruß Thomas
AW: @JogyB
26.07.2010 19:29:27
Thomas
Jogy,
das lässt mir keine ruhe
Tabelle1
 BCDEFGHI
14"Mietvertrag"31.12.200931.12.20100nein22ist
15  31.12.200931.12.2011        ist nicht

verwendete Formeln
Zelle Formel Bereich
D14 =DATUM(WENN(I14="ist nicht";JAHR(HEUTE())+1;JAHR(HEUTE()));MONAT(C14);TAG(C14)) 
F14 =WENN(I14="ist";"nein";"ja") 
H14 =+E14+G14 
D15 =DATUM(WENN(I15="ist nicht";JAHR(HEUTE())+1;JAHR(HEUTE()));MONAT(C15);TAG(C15)) 

Tabellendarstellung in Foren Version 5.29


Ich habe nun am 31.12.2009 zwei Verträge abgeschlossen den einen hab ich gekündigt also "ist"
der andere verlängert sich da "ist nicht" gekündigt um ein weiteres Jahr. Also stimmt es doch, und warum? Weil wir 2010 jetzt haben darum.
Ihr macht mich noch ganz .... in meinem alter g. Zweifel fast schon an meiner Frage die ich stellte.
Aber nun ists gut. Wünsch Dir was Gruß Thomas
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige