Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 10:55:21
Welche Angaben sind Zeile- und Spaltenhöhe
16.04.2024 10:31:56
Bilder in Userform per VBA aus Sharepoint Ordner anzeigen
16.04.2024 10:29:44
Mittelwert für Zeilen mit gleichen Zeitstempel
16.04.2024 09:54:17
Durchschnittswerte zwischen Tagen berechnen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1416to1420
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1416to1420
1416to1420
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Laufzeit bei Unterbrechungen

Laufzeit bei Unterbrechungen
31.03.2015 13:49:35
Dustbin2001
Hallo,
ich habe aktuell die Anforderung, dass ich auf Kundenebene mir die Laufzeit errechnen will. D. h. wie lang ist dieser Kunde bereits im Bestand. Problem sind jedoch Unterbrechungen, welche ich nicht mitzählen möchte.
Aufbau der Tabelle:
(A1-A100) - Kundennummer
(B1-B100) - Vertragsnummer
(C1-C100) - Vertragsbeginn
(D1-D100) - Vertragslaufzeit in Monaten
(E1-E100) - Vertragsende (kann auch in der Zukunft liegen)
Jetzt habe ich den Fall, dass ein Kunde einen Vertrag hatte von bspw. 01.01.2000 - 31.12.2003 (also 4 Jahre). Danach hatte er einige Jahre keinen Vertrag mehr und hat dann einen neuen von 01.01.2012 - 31.12.2014 (also 3 Jahre). In Summe war der Kunde also 7 Jahre im Bestand. Solange ich nur zwei Verträge habe, kann es einfach addiert werden. Wenn nun aber ein Dritter Vertrag hinzukommt, der ab 01.02.2012 beginnt (und somit im gleichen Jahr beginnt wie Vertrag 2) und bis 31.01.2015 läuft, kann ich es nicht mehr addieren. Sonst würde ja eine Laufzeit von 10 Jahren herauskommen. Korrekt sind aber 7 Jahre und 1 Monat.
Danke im Voraus für Eure Hilfe!

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Laufzeit bei Unterbrechungen
31.03.2015 14:46:50
Hajo_Zi

Tabelle2
 H
14

verwendete Formeln
Zelle Formel Bereich N/A
H1{=SUMME(DATEDIF(C1:C14;E1:E14;"Y")*(A1:A14="Kunde1"))}$H$1 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.25 einschl. 64 Bit



Anzeige
so wird nur summiert ...
31.03.2015 15:47:15
der
Hallo Hajo,
... und genau das will ja der Fragesteller nicht.
Die Aufgabe kann noch viel komplexer sein, als man auf dem ersten Blick denkt.
@hallo Dustbin2001,
... was interessiert Dich wirklich? Nur die Zeit in der Verträge liefen ohne die Pausen? Dies würde dann aber im Widerspruch zu der von Dir Eingans gestellten Frage: "wie lang ist dieser Kunde bereits im Bestand" stehen. Diese "Bestandszeit" wäre noch relativ einfach zu ermitteln.
Gruß Werner
.. , - ...

AW: so wird nur summiert ...
31.03.2015 16:08:49
Dustbin2001
Hallo Werner,
mich interessiert wie lange der Kunde bei uns unter Vertrag war - somit nur die Zeit der Verträge ohne Pausen zwischendrin.
Grüße
Dustbin

Anzeige
eine Interimslösung mit Hilfspalte ...
31.03.2015 18:24:12
der
Hallo Dustbin,
... deren Ergebnisse Du auf jeden Fall noch testen musst.
Ich setze voraus, dass die Tabelle vor der Ermittlung sortiert wird und zwar nach Kunden und dann aufsteigend nach "von" und anschließend auch nach "bis".
Die Formeln in der Hilfsspalte und der Ergebnisspalte dann einfach nach unten kopieren:
 ABCDEFGHI
1Kunde von bis  HilfsspalteJahre
2Kunde1 01.01.2000 31.12.2003  1 
3Kunde1 01.01.2001 01.12.2008  7,9 
4Kunde1 01.01.2012 31.01.2015  3,112,0
5Kunde2 01.01.2000 01.12.2003  4,2 
6Kunde2 01.04.2004 01.12.2007  0,8 
7Kunde2 01.01.2005 30.05.2005  2,9 
8Kunde2 01.01.2012 30.06.2015  3,511,4
9         

Formeln der Tabelle
ZelleFormel
H2=RUNDEN((MIN(MAX(E2;E1); C3*(A3=A2)+9^9*(A3<>A2))-C2)/365,25;1)
I2=WENN(A2=A3;"";SUMMEWENN(A:A;A2;H:H))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: eine Interimslösung mit Hilfspalte ...
01.04.2015 16:51:26
Dustbin2001
Hallo Werner,
vielen Dank. Scheint zu funktionieren - die ersten Tests vielen Positiv aus.
Jetzt versuch ich mal die Formel zu verstehen ... :-)
Danke u. Grüße
Dustbin

AW: eine Interimslösung mit Hilfspalte ...
02.04.2015 11:24:53
Dustbin2001
Hallo Werner,
habe doch noch ein Problem aufgedeckt. Und zwar dürfte es an den unterschiedlichen Einzelvertragslaufzeiten innerhalb eines Kunden liegen und der dann nicht mehr passenden Sortierung.
Sortiert wurde nach Deiner Beschreibung (Kunde, Von, Bis).
Hier das Beispiel:
https://www.herber.de/bbs/user/96828.xlsx
Spalte A - E zeigen die vollständige Einzelvertragslaufzeiten und die Ermittlung anhand deiner Formel (ergänzt um eine Wenn-Abfrage). Problem sind die Zeilen 20-22 und vermutlich die Bis-Laufzeit (kürzer als Zeile 19).
Wenn ich diese gelb markierten Daten mal weg lasse, dann habe ich in Spalte G-K bei der gleichen Formel das korrekte Ergebnis, nämlich eine Laufzeit von 12,7 Jahren.
Wie bekomme ich das hin ohne das ich Daten rauswerfe?!?
Danke und Grüße
Dustbin2001

Anzeige
damit hatte ich nun gar nicht gerechnet ...
02.04.2015 16:49:43
der
Hallo Dustbin,
.. ich meine mit einer derartigen Vertragsgestaltung. Unter dem Gesichtspunkt müsste ich mir das noch einmal ganz neu betrachten. heute und die nächsten Tage sicherlich eher nicht.
Sollte der Thread zwischenzeitlich deaktiviert sein, mache einen neuen auf mit Link auf diesen (lesend kann man immer noch zugreifen) auf diesen und einer entsprechende Betreffzeile.
Gruß Werner
.. , - ...

AW: Laufzeit bei Unterbrechungen
31.03.2015 16:06:21
Dustbin2001
Hallo Hajo,
komme noch nicht ganz klar.
Wenn ich die Formel eingebe, habe ich nicht das passende Ergebnis.
Beispiel 1:
A | B | C | D I E | F
Kunde 1 | A | 01.01.2000 | 48 I 31.12.2003 | FORMELERGEBNIS: 3 ERWARTUNG: 4
Beispiel 2:
Kunde 1 | A | 01.01.2000 | 48 I 31.12.2003 | FORMELERGEBNIS: 3 ERWARTUNG: 7
Kunde 1 | A | 01.01.2005 | 36 I 31.12.2007 | FORMELERGEBNIS: 3 ERWARTUNG: 7
Danke!
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige