Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Tage summieren mit unterschiedlichen Zeiträumen
#1
Hallo zusammen,

ich verwalte eine Mitgliederliste, in der ich nachhalten möchte, welches Mitglied wieviel Tage dabei ist oder war. Dabei kann es vorgekommen, dass Mitglieder in der Vergangenheit mehrmals ein- bzw. ausgetreten sind. In einer Zelle ist notiert, zu welchem Datum die Berechnungen erfolgen sollen ("Stand").
Ich habe mittlerweile eine Formel zusammengebastelt, die das wohl auch ausrechnet, möchte aber die Formel-Profis fragen, ob es dafür nicht auch eine kürzere Formel gibt (die Formel aus L6 ist bis nach L11 heruntergezogen).

ABCDEFGHIJKL
1
2
3
4Stand:07.01.2024
5Nr.VornameNameEintritt 1Austritt 1Eintritt 2Austritt 2Eintritt 3Austritt 3Tage
61AntonMustermann14.01.197916.429
72BertaMusterfrau14.01.197931.12.19842.178
84CäsarTestmann01.01.198530.06.198601.01.198714.065
95DoraTestfrau27.01.197901.05.198216.07.198330.05.19841.509
106EmilProbename25.06.198231.12.199101.01.199830.06.200301.01.20206.949
117FriedrichNocheiner05.11.199730.06.200101.01.201531.12.201630.06.201931.12.20223.343

ZelleFormel
L6=WENN(G6="";TAGE($B$4;F6);TAGE(G6;F6))+WENN(H6="";0;WENN(I6="";TAGE($B$4;H6);TAGE(I6;H6)))+WENN(H6="";0;WENN(J6="";0;WENN(K6="";TAGE($B$4;J6);TAGE(K6;J6))))
Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365
Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Danke für's Lesen.


Angehängte Dateien
.xlsx   Tage-Berechnung.xlsx (Größe: 16,39 KB / Downloads: 8)
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Antworten Top
#2
Hallo,

ich würde das so lösen:

Tabelle1

ABCDEFGHIJKLM
3
4Stand:07.01.2024
5Nr.VornameNameEintrittAustrittTageVornameNameTage
61AntonMustermann14.01.197907.01.202416429AntonMustermann16429
72BertaMusterfrau14.01.197931.12.19842178BertaMusterfrau2178
84CäsarTestmann01.01.198530.06.1986545CäsarTestmann14065
95CäsarTestmann01.01.198707.01.202413520DoraTestfrau1509
105DoraTestfrau27.01.197901.05.19821190EmilProbename6949
116DoraTestfrau16.07.198330.05.1984319FriedrichNocheiner3343
126EmilProbename25.06.198231.12.19913476
137EmilProbename01.01.199830.06.20032006
148EmilProbename01.01.202007.01.20241467
157FriedrichNocheiner05.11.199730.06.20011333
168FriedrichNocheiner01.01.201531.12.2016730
179FriedrichNocheiner30.06.201931.12.20221280
Formeln der Tabelle
ZelleFormel
G6=HEUTE()
H6=G6-F6
M6=SUMMENPRODUKT((D6:D17=K6)*(E6:E17=L6)*(H6:H17))
H7=G7-F7
M7=SUMMENPRODUKT((D7:D18=K7)*(E7:E18=L7)*(H7:H18))
H8=G8-F8
M8=SUMMENPRODUKT((D8:D19=K8)*(E8:E19=L8)*(H8:H19))
G9=HEUTE()
H9=G9-F9
M9=SUMMENPRODUKT((D9:D20=K9)*(E9:E20=L9)*(H9:H20))
H10=G10-F10
M10=SUMMENPRODUKT((D10:D21=K10)*(E10:E21=L10)*(H10:H21))
H11=G11-F11
M11=SUMMENPRODUKT((D11:D22=K11)*(E11:E22=L11)*(H11:H22))
H12=G12-F12
H13=G13-F13
G14=HEUTE()
H14=G14-F14
H15=G15-F15
H16=G16-F16
H17=G17-F17

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
[-] Folgende(r) 1 Nutzer sagt Danke an Klaus-Dieter für diesen Beitrag:
  • LuckyJoe
Antworten Top
#3
Hallo,

beim Tabellenaufbau von Klaus-Dieter (Spalte A-H), der m.E. deutlich mehr Sinn macht, könnte man auch einfach eine Pivottabelle verwenden um die Tage zu berechnen.
Gruß
Peter
[-] Folgende(r) 2 Nutzer sagen Danke an Peter für diesen Beitrag:
  • Klaus-Dieter, LuckyJoe
Antworten Top
#4
bei Ausgangskonfiguration L6: =LET(x;SUMME((G6:K6-F6:J6)*ISTUNGERADE(SPALTE(G6:K6)));x+(x<0)*B$4) erlaubt ohne Formelverlängerung noch mehr Ein/Austr..
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
[-] Folgende(r) 1 Nutzer sagt Danke an LCohen für diesen Beitrag:
  • LuckyJoe
Antworten Top
#5
Hallo,

ich hatte mich da auch mal versucht ohne LET.

=SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1))+(ISTUNGERADE(ANZAHL(F6:K6))*$B$4)

Mit LET dann so:

=LET(x;SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1));x+(x<0)*$B$4)


Gruß, Uwe
[-] Folgende(r) 2 Nutzer sagen Danke an Kuwer für diesen Beitrag:
  • LuckyJoe, LCohen
Antworten Top
#6
Hallo zusammen,

vielen Dank für eure Unterstützung.

@Klaus-Dieter, @Peter: Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

@LCohen, @Kuwer: Ich hatte mir auch schon Gedanken um die LET-Funktion gemacht, bin dann aber mit den Formeln nicht weiter gekommen. Normalerweise würde ich das auch über VBA lösen, diesmal soll es aber eine makrofreie Mappe zum Weitergeben bleiben. Mit der LET-Funktion halte ich die Formel für übersichtlich und nachvollziehbar.

Danke nochmals an alle!
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Antworten Top
#7
Hallo Jörg,

Zitat: Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

das kann man möglicherweise unterschiedlich bewerten, ich find die von mir vorgeschlagene Vorgehensweise übersichtlicher. Vor allem ist sie besser für alle Arten der Auswertung geeignet.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
[-] Folgende(r) 1 Nutzer sagt Danke an Klaus-Dieter für diesen Beitrag:
  • LuckyJoe
Antworten Top
#8
Rein normativ hat K-D recht.
WIN/MSO schicken angeblich alle 5 Sekunden Deinen Screen heim zu Papa (recall-Klausel). 
Antworten Top
#9
Hallo LuckyJoe,

hier mal eine Power Query Lösung mit eingebauter M-Funktion.

Gruß von Luschi
aus klein-Paris


Angehängte Dateien
.xlsx   Tage-Berechnung.xlsx (Größe: 20,73 KB / Downloads: 3)
[-] Folgende(r) 1 Nutzer sagt Danke an Luschi für diesen Beitrag:
  • LuckyJoe
Antworten Top
#10
@Klaus-Dieter: ja, grundsätzlich hast du natürlich Recht. In diesem Fall ging es aber um eine andere Darstellung der Tabelle, so dass ich mich für die andere Lösung entschieden habe.

Hallo Luschi,

Danke auch dir für deine interessante Lösung.
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste