Schlagwort: Datumsbereiche

Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:

„Hallo Rene

zum Thema Datumsberechnungen hätte ich gleich eine Frage:

kann Excel auch Zeiträume erkennen, die sich überschneiden, aber unterschiedliche Anfangs- und Endzeiten haben?

Also zum Beispiel:

Mitarbeiter A arbeitet vom 01.05.2021 bis 31.08.2021

Mitarbeiter B arbeitet vom 01.06.2021 bis 15.09.2021

In welchem Zeitraum haben beide gearbeitet

Oder

Von	Bis	Thema
8:00	12:00	Nachdenken
11:30	12:30	Pause
12:30	15:00	Nix tun

Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?

Im ersten Schritt habe ich das visualisiert:

Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:

=WENN(B33<$B$21;0)

Dann die umschließenden Bereiche:

=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)

Und schließlich die überschneidenden Bereiche:

Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)

Analog die zweite Überschneidung:

Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:

=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)

Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:

und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22;B25>=$B$21);B25-$B$21+1;0)

beziehungsweise:

=WENN(UND(B27>=$B$22;B26>=$B$21;B26<=$B$22);$B$22-B26+1;0)

so:

Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:

1. Fall: leere Menge
2. Fall: ganzer Bereich
3. Fall: Überschneidung

1. Fall:

=WENN(ODER(B33<$B$21;B32>$B$22);0)

2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:

=WENN(ODER(B33<$B$21;B32>$B$22);0;WENN(UND(B32>=$B$21;B33<=$B$22);B33-B32+1;0))

2. Fall b: Oktoberfestzeitraum liegt im Datumsbereich:

=WENN(ODER(B31<$B$21;B30>$B$22);0;WENN(UND(B30>=$B$21;B31<=$B$22);B31-B30+1;WENN(UND($B$21>=B30;$B$22<=B31);$B$22-$B$21+1;0)))

Und schließlich die beiden Fälle Nummer 3:

=WENN(ODER(B25<$B$21;B24>$B$22);0;WENN(UND(B24>=$B$21;B25<=$B$22);B25-B24+1;WENN(UND($B$21>=B24;$B$22<=B25);$B$22-$B$21+1;WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0))))

und:

=WENN(ODER(B27<$B$21;B26>$B$22);0;WENN(UND(B26>=$B$21;B27<=$B$22);B27-B26+1;WENN(UND($B$21>=B26;$B$22<=B27);$B$22-$B$21+1;WENN(UND(B26<=$B$21;B27<=$B$22);B27-$B$21+1;WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)))))

Natürlich hätte man die Bedingungen auch umdrehen können.

Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:

1. Fall: Anfang <= Oktoberfest Anfang? Ja:
   Fall a) Ende vor Oktoberfest Anfang?
   Fall b) Ende nach Oktoberfest Ende?
   Fall c) Ende zwischen Oktoberfest Anfang und Ende?
   […]

Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:

Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:

=SEQUENZ(A6-A5+1;1;A5)

Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:

=ZÄHLENWENN($E$1#;C1)

Und diese Kolonne kann man summieren.

Oder in einer Formel:

=SUMME(ZÄHLENWENN($C$1#;SEQUENZ(A6-A5+1;1;A5)))

Geht das auch mit FILTER? Klar:

=FILTER(C1#;(C1#>=A5)*(C1#<=A6))

Oder in einer Formel:

=FILTER(SEQUENZ(A2-A1+1;1;A1);(SEQUENZ(A2-A1+1;1;A1)>=A5)*(SEQUENZ(A2-A1+1;1;A1)<=A6))

Ich bin sicher, dass es für dieses Problem noch weitere Lösungen gibt. Viel Spaß beim Knobeln.

Hallo Rene,
meine Lösungsvorschlag kommt mit einer einer einfachen Formel (ohne Matrixformel) aus.

Das Startdatum des Oktoberfest steht in B2
Das Enddatum des Oktoberfest steht in B3

Der erste Tag von Adele in München steht in B5
Der letzte Tag von Adele in München steht in B6

Dann berechnet folgende Formel die Überschneidung, also die Anzahl von Tagen die Adele auf das Oktoberfest gehen kann.

=ZEILEN(INDEX(A:A;B2):INDEX(A:A;B3) INDEX(A:A;B5):INDEX(A:A;B6))

Im Falle Adele 13 Tage.

Für die anderen Personen muss diese Formel nur entsprechend angepasst werden.
Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus.
Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.

Salü

Ernst

PS: Nachtrag:

Allerdings kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge eingetragen sind.

Also

=ZEILEN(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6))

Ergebnis = 13

{=ZEILE(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6)) }

Ergebnis ( Zellen als kurzes Datum formatiert.):

18.09.2021
19.09.2021
20.09.2021
21.09.2021
22.09.2021
23.09.2021
24.09.2021
25.09.2021
26.09.2021
27.09.2021
28.09.2021
29.09.2021
30.09.2021

Danke Ernst – sehr clever!

Und schließlich hat Helmut Cantzler eine Lösung mit SUMMENPRODUKT gefunden:

=SUMMENPRODUKT((SEQUENZ($B$3-$B$2+1;;$B$2;1)<=B6)*(SEQUENZ($B$3-$B$2+1;;$B$2;1)>=B5))

Danke an Helmut – auch sehr clever!

Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:

    Dim xlBereich1 As Range
    Dim xlBereich2 As Range
    Dim xlSchnittmenge As Range
    With ThisWorkbook.Worksheets("Helmut")
        Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1), 
           .Cells(.Range("B3").Value2, 1))
        Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1), 
           .Cells(.Range("B6").Value2, 1))
    End With
    Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
    MsgBox xlSchnittmenge.Cells.Count

Oder in einem Befehl:

MsgBox Application.Intersect(Range(Cells(Range("B2").Value2, 1), Cells(Range("B3").Value2, 1)), Range(Cells(Range("B5").Value2, 1), Cells(Range("B6").Value2, 1))).Cells.Count

Klappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!

Und schließlich reagiert Christian:

Auch sehr clever! Und sehr elegant! Danke, Christian.