Datumsdiff. - Unterbrech. mit Excel berechnen

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe folgendes Problem und komme nicht dahinter.

Ich bin aktiv im Verein tätig und bin auch der zweite Schriftführer. Ich habe jetzt eine Mitgliederliste in Excel erstellt und möchte jetzt von Excel berechnen lassen, wie lange das jeweilige Mitglied im Verein ist und zwar in Jahren, Monaten und Tagen. Dazu kommt aber noch dass es passieren kann, dass derjenige austreten kann und wieder eintreten. Dieser austritt (Unterbrechung) soll dann von der Gesamten Aktiven Zeit abgezogen werden.

Beispiel:

Eingetreten am 01.02.2008

Ausgetreten am: 10.05.2009

Wiedereingetreten am: 05.03.2010

Ausgetreten am: 12.06.2012

=

Aktive Zeit: 4 Jahre, 0 Monate, 6 Tage!!!

Es sollen die Schaltjahre berücksichtigt und Tag genau berechnet werden!!!

Ich hoffe Ihr könnt mir helfen.

Gruß Rafael

#2

Das ist doch wirklich nicht schwierig, da Excel mit Datumsangaben rechnen kann, ohne dass es besonderer Dinge bedarf. Einfach immer das kleinere Datum vom größeren abziehen. Das Ergebnis sind die Tage dazwischen, wobei Schaltjahre berücksichtigt werden. Dann die beiden Werte addieren, durch 365 teilen und den Rest speichern, diesen dann wieder durch 12 teilen und den Rest = Tage speichern. Das war es.
Gruß
Mike

#3

Hi,

danke für deine Antwort.

Das Passt aber nicht ganz.

Ich hebe jetzt mit folgenden Daten gerechnet:

Eingetreten 01.02.2008
Ausgetreten 12.06.2012
Gesamt 4 Jahre, 4 Monate und 11 Tage

Unterbrechung:

Ausgetreten 10.05.2009
Eingetreten 15.09.2009
Gesamt 0 Jahre 4 Monate und 5 Tage

Aktive Zeit 4 Jahre 0 Monate und 6 Tage!!!

Laut deiner Beschreibung (Schilderung) kommt aber raus
4 Jahre 7 Monate und 4 Tage!!!

#4

Hallo Rafael
Mit den folgenden Formeln kannst Du die Jahre, Monate und Tage berechnen (Ende: A1, Anfang: A2):

jahr: =JAHR(A1)-JAHR(A2)-WENN(ODER(MONAT(A1)>MONAT(A2);UND(MONAT(A1)=MONAT(A2);TAG(A1)>TAG(A2)));0;1)

monat: =REST(MONAT(A1)-MONAT(A2);12)

tag: =WENN(TAG(A1)>TAG(A2);TAG(A1)-TAG(A2);DATUM(JAHR(A1);MONAT(A1);TAG(A1))-DATUM(JAHR(A1);MONAT(A1)-1;TAG(A2)))

Wenn die Mitgliedschaft unterbrochen wurde, wird es kompliziert. Was ist, wenn jemand im einen Jahr 9 Monate und 10 Tage Mitglied war und im anderen 2 Monate und 18 Tage? Ist das Jahr dann voll? Oder erst bei 30 Tagen? Oder bei 31?

Viele Grüsse, Rolf

#5

Hallo Rolf,

danke für deine Antwort, ich habe mal die Formel ein wenig angepasst. Ich füge mal eine Beispieldatei an, wie ich es haben wollte. Es soll immer beachtet werden, welcher Monate es ist und danach richten sich dann die Tage. D. h. Schaltjahr Februar 29 Tage ansonsten 28 Tage, z. B. Januar mit 31 Tagen und z. B. April mit 30 Tagen. Es darf aber nie in den minus Bereich gehen, sondern die Zahl sollte sich z. B. rot färben (wie im vorletzten Beispiel der Datei.

Datei: www.spielmannszug-koenigslutter.de/Beispiel.xlsx

#6

Hallo Rafael,

versuch mal die Formel:

=DATEDIF(C3;C4;„y“)&" Jahre, „&DATEDIF(C3;C4;„ym“)&“ Monate und „&DATEDIF(C3;C4;„md“)&“ Tage."

Das sollte Dir genau das bringen, was Du braucht. Wenn nun mehrere Eintritte und Austritte waren, einfach beide Zeiträume zusammenzählen.

Lieben Gruß

Stefan

#7

Hallo Rafael

die formel lautet zur lösung dein problems ist
=SUMMENPRODUKT((B1-A1)+(B2-A2))
gruß fred

#8

Sorry,

mit Daten habe ich noch nie etwas gemacht,

Gruss

#9

Ich dachte, dass Du über einige Excelkenntnisse verfügst. Die Lösung kannst Du Dir unter folgendem Link abholen:
http://mneumann.eu/download/
Ob es nun 6 oder 7 Tage sind, darüber kann man sicher streiten, aber das Jahr wird nun einmal mit 365 Tagen gerechnet.
Gruß
Mike

#10

Hallo Stefan,

super danke. Ich habe gestern auch erin wenig rumgetüftelt, kam aber nicht wirklich weiter. Die Formel funktioniert soweit ganz gut, aber die Tage bzw. Monate werden nicht richtig gezählt.

Wenn ich z.B. folgende Daten eingebe:

Eintritt: 01.02.2008
Austritt: 10.05.2009
Wiedereintritt: 15.09.2009
Erneuter Austritt: 14.06.2012

Kommt raus, 3 Jahre, 11 Monate, 39 Tage!

Ich habe die Formel so angepasst:

=DATEDIF(A5;B5;„y“)+DATEDIF(C5;D5;„y“)&" Jahr"&WENN(DATEDIF(A5;B5;„y“)+DATEDIF(C5;D5;„y“)1;„e, „;“, „)&DATEDIF(A5;B5;„ym“)+DATEDIF(C5;D5;„ym“)&“ Monat“&WENN(DATEDIF(A5;B5;„ym“)+DATEDIF(C5;D5;„ym“)1;„e, „;“, „)&DATEDIF(A5;B5;„md“)+DATEDIF(C5;D5;„md“)&“ Tag“&WENN(DATEDIF(A5;B5;„md“)+DATEDIF(C5;D5;„md“)1;"e, „;“, ")

1 Like
#11

Hallo Fred,

das ist leider nicht die Lösung, nach der ich gesucht habe, aber trotzdem danke für deine mühe.

Gruß Rafael

#12

Trotzdem DANKE:smile:

#13

Hallo Mike,

ich habe eigentlich ganz gute Excel-Kenntnisse, aber dieser Lösungsvorschlag funktioniert einfach nicht.

Ich habe deine Datei wieder hochgeladen. Ich habe ein paar Daten verändert.

http://www.spielmannszug-koenigslutter.de/Verein.xlsx

#14

Sorry, ich hatte in Zelle H und K anstatt mit 30 mit 12 gerechnet. Habe dies jetzt korrigiert. Datei steht wieder zum Download bereit. Eine kleine Abweichung gibt es jedoch immer noch. Es wird sicher besser, wenn man die Zeiträume separat berechnet. Wie das geht, weißt Du ja jetzt. Fehler wird es dennoch immer geben; z.B. wenn jemand 31 Tage Mitglied war, dann kann das 1 Monat sein, ein Monat und 1 Tag und 1 Monat und 2 bzw. 3 Tage sein. Dies 100prozentig zu berechnen, wäre ein Wahnsinnsaufwand. Da solltest Du einfach einen Blick drauf werfen und es manuell korrigieren.
Gruß
Mike

#15

Hallo Mike,

danke für deine mühe, aber bei ein paar 100 Mitglieder kann ich nicht immer zu nachrechnen und schauen obs passt. ich habe auch daraus eine Liste für Ehrungen erstellt und die Voraussetzung ist, das die Zahlen Passen, ansonsten werden Leute entweder gar nicht oder zu früh geehrt!

Muss ich mal schauen, wie ich jetzt weiter verfahre.

Hab DANK!!!

#16

mögliche Lösung, leider ohne Schaltjahre
A | B | C | D | E | F | G

Eintritt1 | austritt1 | eintritt2 | austritt2 | Jahre | Monate | Tage
01.02.2008 | 10.05.2009 | 05.03.2010 | 12.06.2012 | 3 | 6 | 16

E2: =WENN(B2>0;DATEDIF(A2;B2;„y“)+DATEDIF(C2;D2;„y“);DATEDIF(A2;HEUTE();„y“))
F2: =WENN(B2>0;DATEDIF(A2;B2;„ym“)+DATEDIF(C2;D2;„ym“);DATEDIF(A2;HEUTE();„ym“))
G2: =WENN(B2>0;DATEDIF(A2;B2;„md“)+DATEDIF(C2;D2;„md“);DATEDIF(A2;HEUTE();„md“))

Gruß
Brandis

#17

Hi Brandis,

klappt leider nicht ganz. Wenn ich austritt2 z.B. auf 15.09.2009 setze, bekomme ich als Ergebnis: 3 Jahre, 11 Monate, 37 Tage!
Richtig wäre aber: 4 Jahre, 0 Monate, 6 Tage!!!

Hab trotzdem DANK:smile:!

#18

Hallo Rafael,
für Dein Problem würde ich Dir die wenig dokumentierte Funktion DATEDIF vorschlagen.

Der Syntax lautet:
datedif(Zieldatum;Ausgangsdatum;„Art der Darstellung“).

Das Zieldatum muss natürlich größer sein als das Ausgangsdatum. Die Art der Darstellung muss in Anführungszeichen einsetzen werden. Dabei bedeutet:
Y = Jahr; m = Monat; d = Tag (Groß/klein ist egal).

Zu beachten ist auch, dass z.B. der 1.1.2012 bis zum 1.1.2013 ein ganzen Jahr, 12 Monate oder 365 Tage je nach Art der Darstellung ergibt. Schaltjahre werden berücksichtigt.

Mit dieser einfachen Funktion kannst Du auch mit wenig Excel-Wissen eine zuverlässige Übersicht gestalten.
Der Rest ist ein wenig Fleißarbeit. Aber das Tüfteln ist ja das Salz in der Excel-Suppe. Probiere ruhig mal diese einfache Funktion aus! Einfacher geht es kaum.

Selbst benutze ich diese Funktion für meine Geburtstagetabelle und ich habe seit dem immer die richtigen Altersangaben bekommen.

Viel Erfolg wünscht Roland57

#19

Hallo Rafael,

freut mich, dass ich helfen konnte.

Ich verstehe zwar nicht genau, warum Du schreibst, dass die Monate und Tage nicht richtig berechnet werden. Aber Hauptsache es geht.

Was mir aber noch eingefallen ist: Mit der Formel kann es vorkommen, dass z.B. 2J 13M 10T ausgegeben wird. Weil Du ja die 2 Mitgliedschaften zusammenzählst. Da müßtest Du noch irgendwie 12 Monate abziehen und dafür ein Jahr dazu zählen. Ich würde daher vorschlagen die Formel aufzuteilen nach J, M, T. Dann erst zusammenzählen, ggf. addieren und dann erst als Text ausgeben. Oder noch besser gar nicht als Text ausgeben, sonder J,M,T in verschiedenen Zellen stehen lassen. Bei Monat scheint es mir noch einfach, bei Tagen jedoch nicht. Sollen 30 oder 31 Tage als ganzer Monat gerechnet werden? Ist bei 2 Mitgliedschaften eigentlich nicht klar zu definieren.

Lieben Gruß

Stefan

#20

Hi Stefan,

ja genau deswegen, was du auch angemerkt hast, dass es vorkommen kann, dass 13 Monate oder auch mehr als 28/29 oder 30 bzw 31 Tage errechnet werden. Bei dem Beispiel den ich dir aufgeschrieben habe, kam ja raus 3 Jahre, 11 Monate, 39 Tage!!! Das passt ja nicht, es müssten 4 Jahre, 0 Monate und 8 Tage sein!!! Ich hoffe ich bekomme noch den richtigen Geistesblitz!?

Gruß Rafael