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 in einem Zeitraum bestimmen und addieren
#1
Hallo,

ich möchte die Anzahl der anwesenden Tage von Personen berechnen,
welche innerhalb eines Zeitraumes liegen und diese gleich automatisch addieren?

Es gibt für jede Person ein Anreise- und Abreisedatum:

Personen Einzugsdatum Auszugsdatum
Person 1: 22.12.14 - 13.05.15
Person 2: 22.03.15 - 01.05.15
Person 3: 22.01.14 - 04.02.15
Person 4: 01.01.14

Und einen vorgegebenen Zeitraum welcher für die Tage relevant ist: 01.01.2015-28.02.2015

In diesem Fall würden also die Tage von:
Person 1: mit 1.1.2015-28.2.2015
Person 2: 0
Person 3: mit 1.1.2015-04.02.2015
Person 4: mit 1.1.2015-28.2.2015
gerechnet werden.

Mit SUMMENPRODUKT und DATEDIF erscheint mir das recht kompliziert, da ich recht viele Abfragen machen muss, oder?

Hat jemand eine Idee, ob man das irgendwie einfach gestalten kann?
Ich habe leider noch nicht so richtig den Durchblick bei komplexeren Formeln in Excel und wäre für jede Hilfe dankbar...


Angehängte Dateien Thumbnail(s)
   
Antwortento top
#2
Hallo syquest

Meinst du es so?
 ABCDEFG
1PersonenEinzugsdatumAuszugsdatumAnwesenheit Zeitraum 
2A22.12.201413.05.201558 01.01.201528.02.2015
3B22.03.201501.05.20150   
4C22.01.201404.02.201534   
5D01.01.2014 58   

ZelleFormel
D2=MAX(0;MIN(C2;$G$2)-MAX(B2;$F$2))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Antwortento top
#3
(16.07.2015, 05:42)shift-del schrieb: Meinst du es so?

Vielen Dank erstmal für die Antwort!

Ja, im Grunde genommen schon, nur sollte das Ergebnis nicht als einzelne Einträge sondern gleich alles zusammengerechnet als ein Wert sein.
D.h. in dem Falle würde nur die Zahl 150 rauskommen.
Ich habe es gerade mit
=SUMMENPRODUKT((B2:B5<>"")*(MAX(0;(MIN(C2:C5;$G$2)+1)-MAX(B2:B5;$F$2))))
probiert, da kommt allerdings nur 0 raus.

Naja, gut, wenn ich mir das so anschaue, kann es nicht klappen, da er in meiner abgeänderten Formel ja nur jeweils MIN & MAX einer Spalte (B bzw. C) berechnet und nicht die Formel für jede Zeile einzeln, richtig?

Wie ist denn dafür der Lösungsansatz, wenn das mit MIN & MAX nicht als array klappt?
Antwortento top
#4
Hola,

nur um Doppelarbeit zu vermeiden....

http://www.herber.de/forum/messages/1436631.html

Gruß,
steve1da
Antwortento top
#5
Zitat:nur sollte das Ergebnis nicht als einzelne Einträge sondern gleich alles zusammengerechnet als ein Wert sein.
D.h. in dem Falle würde nur die Zahl 150 rauskommen.
Tja, warum auch den einfachen Weg nehmen.
Geht bestimmt auch kürzer.
Code:
{=SUMME(WENN(WENN(C2:C5<=$G$2;WENN(C2:C5="";$G$2;C2:C5);$G$2)<WENN(B2:B5>=$F$2;B2:B5;$F$2);0;WENN(C2:C5<=$G$2;WENN(C2:C5="";$G$2;C2:C5);$G$2)-WENN(B2:B5>=$F$2;B2:B5;$F$2)))}
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
  • syquest
Antwortento top
#6
Zitat:Geht bestimmt auch kürzer.
Länge halbiert.
Code:
=SUMMENPRODUKT((C2:C5>F2)*(B2:B5<F2)*(G2-F2-((G2-C2:C5)>0)*(G2-C2:C5)))
Voraussetzung ist dass es in Spalte C keine leeren Zellen geben darf; d.h. man trägt dort 31.12.9999 ein.
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
  • syquest
Antwortento top
#7
Hallo,

eine etwas längere Formel, die aber mit Leerzellen in C umgehen kann:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFG
1     Startdatum01.01.2015
2     Endedatum28.02.2015
3PersonEinzugAuszugTage   
4Person 122.12.201413.05.201559 116 
5Person 222.03.201501.05.20150   
6Person 322.01.201430.01.201435   
7Person 401.01.2014 59   

ZelleFormel
F4{=SUMME(WENN(C4:C7="";G2;WENN(C4:C7>G2;G2;WENN(C4:C7<G1;0;C4:C7)))-WENN((C4:C7<G1)*(C4:C7<>"");0;WENN(B4:B7<G1;G1;WENN(B4:B7>G2;G2;B4:B7))))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Benutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • syquest
Antwortento top
#8
(17.07.2015, 04:29)shift-del schrieb:
Zitat:Geht bestimmt auch kürzer.
Länge halbiert.
Code:
=SUMMENPRODUKT((C2:C5>F2)*(B2:B5<F2)*(G2-F2-((G2-C2:C5)>0)*(G2-C2:C5)))
Voraussetzung ist dass es in Spalte C keine leeren Zellen geben darf; d.h. man trägt dort 31.12.9999 ein.

Das ist mal übersichtlich! Danke!

Ich schaue mir mal beide Formeln an, und versuche die zu verstehen. Allerdings werde ich wahrscheinlich doch die längere benutzen, da Spalte C auf jeden Fall leere Zellen enthalten wird. Die Excel Tabelle ist nicht für mich, und der Übersicht halber und aus anderen Gründen gibt es leider die leeren Zellen. Aber vielen Dank für deine Hilfe! Ist auch für mich zum Verstehen und Lernen wichtig.
Antwortento top
#9
(17.07.2015, 08:07)BoskoBiati schrieb: eine etwas längere Formel, die aber mit Leerzellen in C umgehen kann:

Danke dir, Edgar!
Antwortento top
#10
Was allerdings bei den Formeln merkwürdig ist,

Bei einer kommt:

1) C 22.01.2014 - 04.02.2015 : 34 Tage

und bei der anderen:

2) Person 3 22.01.2014-30.01.2014: 35 Tage
raus.

Bei 2) müssten es eigentlich 0 Tage sein, da es sich um 2014 handelt, bei 1) müssten es 14 Tage sein, oder irre ich mich?
Ich schau nochmal....
Antwortento top


Gehe zu:


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