Supportnet / Forum / Tabellenkalkulation
Dauer der Vertragslaufzeit im laufenden Jahr ermitteln
Frage
Hallo Community,
komm in Excel mit einer Datumformel nicht weiter. In zwei nebeneinanderliegenden Zellen ist die erste (z.B: A1) der Beginn und (z.B.) B1 das Ende einer Vertragslaufzeit. Ermitteln möchte ich nun die Dauer der Monate, in der das Vertragsverhältnis (im aktuellen Jahr) bestanden hat.. Folgende Fallkonstellationen sind dabei möglich:
z.B:
A1 ------------- B1
01.01.1995 <leer> Ergebnis: 8 (Monate)
01.02.2003 <leer> Ergebnis: 7
01.01.1995 31.12.2002 Ergebnis: 0
01.02.2003 30.06.2003 Ergebnis: 6
01.01.1995 31.10.2003 Ergebnis: 8 (Berechnung soll nur bis zum aktuellen Monat
erfolgen)
Die Varianten 1-2 kann ich mit folgender Formel erschlagen:
= WENN(JAHR(A1)<JAHR(HEUTE());MONAT(HEUTE());MONAT(HEUTE()-A21))
Mehr hab ich selbst nicht auf die Reihe gebracht, Vielleicht weiss jemand noch eine andere Möglichkeit, würde mich freuen
Gruß
martl
Antwort 1 von want2cu
hallo martl,
die Formel lautet:
=WENN(JAHR(B2)>=JAHR(HEUTE());MIN(MONAT(B2);MONAT(HEUTE()));0)-(WENN(JAHR(A2)<JAHR(HEUTE());0;WENN(B2="";0;MONAT(A2)-1)))
CU
want2cu
die Formel lautet:
=WENN(JAHR(B2)>=JAHR(HEUTE());MIN(MONAT(B2);MONAT(HEUTE()));0)-(WENN(JAHR(A2)<JAHR(HEUTE());0;WENN(B2="";0;MONAT(A2)-1)))
CU
want2cu
Antwort 2 von AS
Hallo,
schön wär's, want2cu! :-(
Ich habe es mir vor einer halben Stunde angesehen und seitdem habe ich gegrübelt!
Der Martl hat uns ein faules Ei gelegt.
Bis jetzt habe ich nur eine unvollkommene Lösung:
=WENN(A1="";"";DATEDIF(A1;WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Formel runterkopieren.
Was ich noch nicht lösen konnte, ist der Datensatz
1.1.1995 - 31.12.2002
Hier sollte eine Leerzelle als Ergebnis erscheinen.
Vielleicht findet jemand noch die Lösung für diesen Knackpunkt. Heute komme ich nicht mehr dazu.
Seid alle gegrüßt!
Arnim
schön wär's, want2cu! :-(
Ich habe es mir vor einer halben Stunde angesehen und seitdem habe ich gegrübelt!
Der Martl hat uns ein faules Ei gelegt.
Bis jetzt habe ich nur eine unvollkommene Lösung:
=WENN(A1="";"";DATEDIF(A1;WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Formel runterkopieren.
Was ich noch nicht lösen konnte, ist der Datensatz
1.1.1995 - 31.12.2002
Hier sollte eine Leerzelle als Ergebnis erscheinen.
Vielleicht findet jemand noch die Lösung für diesen Knackpunkt. Heute komme ich nicht mehr dazu.
Seid alle gegrüßt!
Arnim
Antwort 3 von AS
Hallo Martl,
es hat mir keine Ruhe gelassen und ich habe nach meiner Rückkehr evtl. eine Lösung zumindest für das laufende Jahr gefunden:
=WENN((A1="")+(B1<>0)*(B1<=DATUM(2003;1;));"";DATEDIF(A1;WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Formel runter kopieren.
Wenn Du in Deiner Tabelle z.B. in E1 das laufende Jahr einträgst, kannst Du in der Formel anstatt "2003" den Zellnamen $E$1 eintragen.
Gruß Arnim
es hat mir keine Ruhe gelassen und ich habe nach meiner Rückkehr evtl. eine Lösung zumindest für das laufende Jahr gefunden:
=WENN((A1="")+(B1<>0)*(B1<=DATUM(2003;1;));"";DATEDIF(A1;WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Formel runter kopieren.
Wenn Du in Deiner Tabelle z.B. in E1 das laufende Jahr einträgst, kannst Du in der Formel anstatt "2003" den Zellnamen $E$1 eintragen.
Gruß Arnim
Antwort 4 von AS
Hallo Martl,
nun noch etwas. Nachdem ich in Ruhe alles ansehen konnte, ist mir noch ein "faules Ei" aufgefallen. Wenn das Beginn-Datum aus früheren Jahren stammt und mit einem späteren Monat als Januar beginnt, stimmt meine obige Formel nicht mehr. Ich habe sie also noch ein bisschen erweitert:
=WENN((A1="")+(B1<>0)*(B1<=DATUM(2003;1;));"";DATEDIF(WENN(JAHR(A1)<JAHR(HEUTE());DATUM(2003;1;1);A1);WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Ich bin überzeugt, dass man sie etwas vereinfachen und verkürzen könnte, aber dazu habe ich jetzt keine Lust mehr!
Gruß Arnim
nun noch etwas. Nachdem ich in Ruhe alles ansehen konnte, ist mir noch ein "faules Ei" aufgefallen. Wenn das Beginn-Datum aus früheren Jahren stammt und mit einem späteren Monat als Januar beginnt, stimmt meine obige Formel nicht mehr. Ich habe sie also noch ein bisschen erweitert:
=WENN((A1="")+(B1<>0)*(B1<=DATUM(2003;1;));"";DATEDIF(WENN(JAHR(A1)<JAHR(HEUTE());DATUM(2003;1;1);A1);WENN((B1=0)+(B1>HEUTE());HEUTE();B1);"ym")+1)
Ich bin überzeugt, dass man sie etwas vereinfachen und verkürzen könnte, aber dazu habe ich jetzt keine Lust mehr!
Gruß Arnim
Antwort 5 von want2cu
Hallo Arnim,
klasse gelöst!
Tja, da wahr ich wohl im Eifer des Gefechts zu unaufmerksam und habe den Wald vor lauter Bäumen nicht mehr gesehen.
SORRY!
Aber zum Glück gibt es ja andere, aufmerksame und topfitte Excelianer hier im Sn ;-)
CU
want2cu
klasse gelöst!
Tja, da wahr ich wohl im Eifer des Gefechts zu unaufmerksam und habe den Wald vor lauter Bäumen nicht mehr gesehen.
SORRY!
Aber zum Glück gibt es ja andere, aufmerksame und topfitte Excelianer hier im Sn ;-)
CU
want2cu
Antwort 6 von AS
Hallo want2cu,
ich bin nicht heimlich, sondern schon unheimlich fromm! Daher gebe ich Dir den biblischen Rat (Math.5), dass man sein Licht nicht unter den Scheffel stellen soll!
Was mich betrifft: Auch ein blindes Huhn ...usw.
Ein schönes Wochenende!
Arnim
ich bin nicht heimlich, sondern schon unheimlich fromm! Daher gebe ich Dir den biblischen Rat (Math.5), dass man sein Licht nicht unter den Scheffel stellen soll!
Was mich betrifft: Auch ein blindes Huhn ...usw.
Ein schönes Wochenende!
Arnim
Antwort 7 von want2cu
Hallo Arnim,
das hat jetzt nichts mit Excel zu tun, aber ich möchte einfach nochmal nachfragen, ob ich das richig verstanden habe: du sagst von dir, dass du nicht heimlich fromm, sondern "unheimlich fromm" bist und gibst mir einen biblischen Hinweis.
Wenn das heisst, dass du Christ bist, dann freut mich das besonders. Ich bin auch Christ und will das auch ganz offen hier im SN sagen. Vieles von dem, was ich tue, ist von meinem Glauben her bestimmt.
Damit es niemand in den falschen Hals kriegt: Christen sind keineswegs bessere Menschen, sie haben nur eine andere Perspektive ;-)
Ich wünsche ebenfalls ein schönes Wochenende und in diesem besonderen Fall einen gesegneten Sonntag
Cu
Klaus Stachuletz
alias want2cu
das hat jetzt nichts mit Excel zu tun, aber ich möchte einfach nochmal nachfragen, ob ich das richig verstanden habe: du sagst von dir, dass du nicht heimlich fromm, sondern "unheimlich fromm" bist und gibst mir einen biblischen Hinweis.
Wenn das heisst, dass du Christ bist, dann freut mich das besonders. Ich bin auch Christ und will das auch ganz offen hier im SN sagen. Vieles von dem, was ich tue, ist von meinem Glauben her bestimmt.
Damit es niemand in den falschen Hals kriegt: Christen sind keineswegs bessere Menschen, sie haben nur eine andere Perspektive ;-)
Ich wünsche ebenfalls ein schönes Wochenende und in diesem besonderen Fall einen gesegneten Sonntag
Cu
Klaus Stachuletz
alias want2cu
Antwort 8 von martl
Hallo want2cu und Arnim,
vorab möchte ich mich für die ausführliche und sicherlich zeitraubende Problemlösung bei Euch sehr herzlich bedanken und Euch ein großes Kompliment für die schnelle und kompetente Fehleranalyse und –behebung aussprechen. Bitte entschuldigt, wenn Ihr durch mich so zum grübeln gekommen seid.
Also, die Formel funktioniert perfekt. Hab mir auch den Inhalt der Formeln angesehen, und versuche im Moment noch, diese auch zu verstehen, das kann allerdings noch ein Weilchen dauern ;). Auf diese Lösung, bzw. auch die anderen Lösungsvorschläge wäre ich nicht gekommen.
Beim Lesen Eurer Antworten, fiel mir folgender Satz ein: „Es gibt nichts Gutes, es sei denn man tut es.“ Es freut mich, dass ihr nicht nur Christen seid, sondern auch so lebt und mit Eurem Wissen anderen hilfsbereit zur Seite steht.
Auch ich möchte Euch noch einen gesegneten Sonntag wünschen
Viele Grüße
Martin
vorab möchte ich mich für die ausführliche und sicherlich zeitraubende Problemlösung bei Euch sehr herzlich bedanken und Euch ein großes Kompliment für die schnelle und kompetente Fehleranalyse und –behebung aussprechen. Bitte entschuldigt, wenn Ihr durch mich so zum grübeln gekommen seid.
Also, die Formel funktioniert perfekt. Hab mir auch den Inhalt der Formeln angesehen, und versuche im Moment noch, diese auch zu verstehen, das kann allerdings noch ein Weilchen dauern ;). Auf diese Lösung, bzw. auch die anderen Lösungsvorschläge wäre ich nicht gekommen.
Beim Lesen Eurer Antworten, fiel mir folgender Satz ein: „Es gibt nichts Gutes, es sei denn man tut es.“ Es freut mich, dass ihr nicht nur Christen seid, sondern auch so lebt und mit Eurem Wissen anderen hilfsbereit zur Seite steht.
Auch ich möchte Euch noch einen gesegneten Sonntag wünschen
Viele Grüße
Martin