Dauer einer Beziehung anhand Datum ermitteln

[Mahadmakandis]

Moin Moin,

ich habe zu meinem bearbeiteten Problem schon 2 Anfragen gestellt, daher dürfte die Problembeschreibung dem einen oder anderen bekannt vorkommen.
Ich möchte für eine Patentanalyse mir Interaktionen zwischen verschiedenen Erfindern anschauen, die an ein und dem selben Patent gearbeitet haben.

Ein Patent kann 1, 2, 3, ... x Erfindern haben. Daraus ergeben sich eine feste Anzahl von 2er-Interaktionen.

Hier stehen die Zahlen vor und hinter dem "_" für einen Erfinder.
Duration_Basis

	A
1	interactions
2	03873270-2_04028639-2
3	03873270-2_05072199-1
4	03873270-2_05546050-2
5	03875505-1_04004756-1
6	03875505-1_04004756-1

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Jedes Patent besitzt eine Patentnummer sowie ein Datum, an dem es erteilt wurde.

Duration_Basis

	A	B	C
1	interactions	docdb_family_id	family_date
2	03873270-2_04028639-2	21761632	27.01.1998
3	03873270-2_05072199-1	21761632	27.01.1998
4	03873270-2_05546050-2	21761632	27.01.1998
5	03875505-1_04004756-1	15130047	22.11.1974
6	03875505-1_04004756-1	15130120	22.11.1974

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Wie man hier in Zeile 5-6 sieht, ist die Interaktion 03875505-1_04004756-1 in 2 verschiedenen Patenten aufgetreten.
Hier haben diese Erfinder also an 2 verschiedenen Zeiträumen miteinander gearbeitet.
Mein Ziel ist es jetzt also herauszufinden, wielange diese Personen miteinander gearbeitet haben.
An sich kann ich die Zellen mit dem Datum voneinander subtrahieren und erhalte damit den Zeitraum.
Da es sich aber um 830 Zeilen handelt suche ich nach einer Formel, einem Makro oder einer Möglichkeit das ganze automatisiert durchführen zu lassen.

Ich möchte also das von Zeile 1 überprüft wird, ob es die Interaktion in der aktuell stehenden Zeile mehrmals gibt.
Wenn ja, soll er die Differenz aus dem frühesten Datum und dem spätesten Datum dieser mehrfachen Interaktionen bilden. Hier also DIfferenz aus 22.11.1974-22.11.1974.
Hier ist bereits die nächste Besonderheit. Wenn diese Differenz dann 0 ist, soll ein fester Wert wie s.u. eingetragen werden.
Wenn nein, soll er einen festen Wert eintragen (Der steht aktuell noch nicht fest, kann aber 1 Jahr, 12 Monat oder 365 Tage sein.)


Ich habe euch die Datei angehängt und beispielhaft die Ergebnisse für die ersten Interaktionen eingetragen.

PS. Wenn es eine Möglichkeit gibt, lässt es sich später einfach ändern ob die Differenz in Tagen, Monaten oder Jahren angegeben werden soll?

Beste Grüße,
MahadmaKandis

[gbr]

Hallo,

Ich habe in der Zelle F1 einen festen Wert eingegeben da dieser aktuell noch nicht feststeht.

Die Formel in D2 sieht dann so aus:  

{=WENN(MAX(WENN($A$2:$A$1000=$A2;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A2;$C$2:$C$1000))=0;$F$1;MAX(WENN($A$2:$A$1000=$A2;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A2;$C$2:$C$1000)))}

Da es sich hier um eine Array-Formel handelt werden die Formeln in geschweiften Klammern angezeigt.
Diese Klammern nicht mit eingeben.
Abschluss der Formeleintragung mit Strg + Shift + Enter
Die Formeln können wie alle anderen auch runterkopiert werden.
Hier ist der Bereich bis 1000 eingetragen.
Solltest du mehr Zeilen benötigen ggf. anpassen.

Viel Spaß damit
Günter

[Mahadmakandis]

Moin,

deine Formel scheint zu funktionieren!

Es ist richtig, dass er aktuell die DIfferenz in Tagen ausgibt. Ist es möglch das auch in Wochen, Monaten oder Jahren anzugeben?

Beste Grüße und vielen Dank schonmal!

[gbr]

Hallo,

ich habe einfach das Ergebnis in C3 als Datumszahl angesehen:

Jahre:     =JAHR(C3)-1900     ( -1900 weil Excel je nach Einstellung mit dem Jahr 1900 beginnt)
Monate:  =MONAT(C3)-1       (  -1 weil im Datum jeweils der begonnene Monat angezeigt wird, du brauchst die vollendeten)
Tage:      =TAG(C3)


Egal wie du rechnest, du könntest ja auch C3 durch 365,xxx teilen, du bekommst fast immer einen Fehler in die Rechnung! 
Bei der obigen Berechnung fängt alles am 01. Januar an.
Der Februar ist immer der kürzeste Monat, den hast du in der Berechnung fast immer dabei.
Das Schaltjahr verschiebt dir zusätzlich, ggf. ungewollt, die Berechnung um einen Tag.
Wie hoch die Fehler sind und ob du damit leben kannst solltest du an den echten Zahlen überprüfen.

Viele Grüße
Günter

[Mahadmakandis]

Hallo,

ich habe einfach das Ergebnis in C3 als Datumszahl angesehen:

Jahre:     =JAHR(C3)-1900     ( -1900 weil Excel je nach Einstellung mit dem Jahr 1900 beginnt)
Monate:  =MONAT(C3)-1       (  -1 weil im Datum jeweils der begonnene Monat angezeigt wird, du brauchst die vollendeten)
Tage:      =TAG(C3)


Egal wie du rechnest, du könntest ja auch C3 durch 365,xxx teilen, du bekommst fast immer einen Fehler in die Rechnung! 
Bei der obigen Berechnung fängt alles am 01. Januar an.
Der Februar ist immer der kürzeste Monat, den hast du in der Berechnung fast immer dabei.
Das Schaltjahr verschiebt dir zusätzlich, ggf. ungewollt, die Berechnung um einen Tag.
Wie hoch die Fehler sind und ob du damit leben kannst solltest du an den echten Zahlen überprüfen.

Viele Grüße
Günter

Ok, das heißt aber ich müsste eine neue Spalte machen, die das Datum aus $C$ formatiert in das neue Format und dann deine Formel auf die neue Spalte anpassen?

Eine weitere Frage gibt es noch zu deiner 1. Formel. Gibt es dort die Möglichkeit die DIfferenz immer zusätzlich plus den festen Wert zu rechnen?
Wenn ich z.b. 365 Tage als festen Wert nehme:
Wenn keine Differenz auftaucht wird 365 ausgegeben. Wenn allerdings eine Differenz von 30 Tagen festgestellt wird soll die DIfferenz + fester wert ausgegeben werden.
In diesem Fall also 365+30 damit eine Vergleichbarkeit geschaffen wird. Wo muss ich das in der Formel ergänzen?

Beste Grüße,
MahadmaKandis

[gbr]

Hallo ,

zuerst zur zweiten Frage:
Ich verstehe den Sinn der 365 Tage nicht.
Die Vergleichbarkeit ist mit den Originalzahlen sehr viel genauer.
Aber vielleicht hat's ja einen tieferen Sinn.
Die gewünschten Formel ist jetzt sogar einfacher weil der Vergleich auf 0 entfällt:

=MAX(WENN($A$2:$A$1000=$A2;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A2;$C$2:$C$1000))+365

Das ganze wieder mit   Strg  + Umsch  +  Enter    abschließen.
Es müssen dann die geschweiften Klammern erscheinen.

Nun zum ersten Teil:
Ein Fehler hatte sich eingeschlichen: Die Ergebnisse stehen jeweils in der Spalte D, nicht wie in meinen Formeln angegeben in C

Ich habe in D6 folgende Formel eingetragen:

=JAHR(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1900& " Jahre, "& MONAT(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1 & " Monate und " & TAG(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365) & " Tage"

Da hättest du das Ergebnis komplett in einer Zelle.
Für weitere Berechnungen, Filtereinstellungen oder Pivottabellen bringt das jedoch Probleme.

Ansonsten kannst du die oben angeführten Formeln in die Spalten E, F und G eintragen.
D muss nicht als Datum formatiert sein.

Eine weitere Alternative wäre die Formeln Direkt in die Spalten D, E und F einzutragen:

=JAHR(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1900
=MONAT(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1
=TAG(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)


Alle Formeln  wieder mit   Strg  + Umsch  +  Enter    abschließen.
Es müssen dann die geschweiften Klammern erscheinen.

Nach den obigen Formeln bekommst du übrigens bei 365  0 Jahre, 11 Monate und 30 Tage  raus.
Das Jahr 1900 ist ein Schaltjahr.

Gruß Günter

[Mahadmakandis]

Hallo ,

zuerst zur zweiten Frage:
Ich verstehe den Sinn der 365 Tage nicht.
Die Vergleichbarkeit ist mit den Originalzahlen sehr viel genauer.
Aber vielleicht hat's ja einen tieferen Sinn.
Die gewünschten Formel ist jetzt sogar einfacher weil der Vergleich auf 0 entfällt:

Moin,
vielleicht hast du garnicht unrecht. Meine Überlegung war, dass ich mit 0 später nicht weiter arbeiten kann. Ich werde mit SPSS, einem Statistik-Tool, ein paar Auswertungen anstellen und habe befürchtet, dass durch die Dauer "0" falsche Ergebnisse entstehen. Aber da sich alles nur um +365 verschiebt ist das ja eine lineare Transformation die eigentlich keine Bedeutung hat..

Ich habe in D6 folgende Formel eingetragen:

=JAHR(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1900& " Jahre, "& MONAT(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365)-1 & " Monate und " & TAG(MAX(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))-MIN(WENN($A$2:$A$1000=$A5;$C$2:$C$1000))+365) & " Tage"

Diese Formel hilft mir ganz gut für die Darstellung. Wenn ich nun deinen Tipp annehmen und das ganze wieder auf 0 setzen möchte, reicht es dann die +365 überall zu entfernen?

Beste Grüße und vielen Dank für deine Hilfe!

[Mahadmakandis]

Wichtiger Edit!

Ich weiß wieder warum ich 365 oder einen festen Wert gewählt habe.

Da ich später die "Emotionale Nähe" als einen Quotienten aus der "Häufigkeit einer Interkation/die Duration" darstellen möchte, darf die Duration nicht 0 sein.
Das würde nämlich alle Ergebnisse verzerren, da durch 0 nicht geteilt werden kann.