Zeiträume vergleichen

Forumthread: Zeiträume vergleichen

philG

Liebe Herber-Gemeinde,
ich hoffe mir kann bei folgendem Problem geholfen werden, bin langsam am verzweifeln:
Ich habe einen Zeitraum, der in 2 Zellen abgelegt ist (von-bis-Datumsangaben), dann habe ich in 2 weiteren Zellen einen Vergleichzeitraum und möchte checken, ob dieser innerhalb des 1. Zeitraums liegt.
Ich habe die Datumfelder nicht als solche formatiert, es stehen also die Zahlenwerte darin, z.B. (Zelle):
Start (B) Ende (C) Datum (E) Datum (F)
32782 32963 32876 32898
Dazu habe ich folgende Formel entworfen:

=WENN(ODER(UND(E2>=$B$2;F2=$B$3;F2>=$C$3);UND(E2>=$B$4;F2>=$C$4);UND(E2>=$B$5;   F2>=$C$5);UND(E2>=$B$6;F2>=$C$6));"o.k.";"Bereich größer als ein Winter oder Sommer Halbjahr")

..leider funktioniert diese nur bis Zeile 31, danach geht nichts mehr.
Es wäre wirklich super, wenn mir jemand mit einer Lösung weiterhelfen könnte!
Vorab schonmal vielen Dank!!!

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Zeiträume vergleichen

18.01.2012 10:15:26

Rudi

Hallo,

	B	C	D	E	F	G
2	15.01.12	30.03.12		18.01.12	20.03.12	OK
3				05.01.12	20.03.12	nicht OK
4				16.01.12	05.04.12	nicht OK

Zelle	Formel
G2	=WENN((MIN(E2:F2)>=MIN($B$2:$C$2))*(MAX(E2:F2)<=MAX($B$2:$C$2));"OK";"nicht OK")
G3	=WENN((MIN(E3:F3)>=MIN($B$2:$C$2))*(MAX(E3:F3)<=MAX($B$2:$C$2));"OK";"nicht OK")
G4	=WENN((MIN(E4:F4)>=MIN($B$2:$C$2))*(MAX(E4:F4)<=MAX($B$2:$C$2));"OK";"nicht OK")

Gruß
Rudi

AW: Zeiträume vergleichen

18.01.2012 10:32:20

philG

Vielen Dank aber das ist nur eine Abwandlung meiner bisherigen Lösung, mir wäre daran gelegen, dass mit über 32 Zeiträumen verglichen wird (die linken beiden Spalten). Bisher gelöst mit Oder-Verküpfung aber davon kann Excel maximal 32 behandeln.
Hat da jmd. eine Lösung?

verstehe nicht. owT

18.01.2012 10:41:43

Rudi

AW: verstehe nicht. owT

18.01.2012 11:04:34

philG

Spalte B: Startdatum eines Halbjahres
Spalte C: Enddatum eines Halbjahres
Spalte B und C enthalten zeilenweise für 42 Sommer- bzw. Winterhalbjahre das Anfangs- und Enddatum
Spalte E und F enthalten das Start- bzw. Enddatum verschiedener Zeiträume. Insgesamt liegen über 100 solcher Zeiträume vor - diese sind also zeilenweise in die Spalten E und F eingetragen.
Ziel: überprüfe für jeden Zeitraum (Spalte E-F), ob er in einem Halbjahr liegt (Ergebnismeldung "o.k."), oder ob er zwei oder mehrere Halbjahre überschneidet (Ergebnismeldung "nicht o.k." o.ä.).
Urpsrünglicher Lösungsansatz:

WENN(ODER(UND(2>=$B$2;F2=$B$3;F2>=$C$3);UND(E2>=$B$4;F2>=$C$4);UND(E2>=$B$5;F2>=$C$5);UND(E2>=$B$6;F2>=$C$6));"o.k.";"nicht o.k.")

Obige Formel liefert folgendes Problem:
Die in der ODER-Verknüpfung enthaltenen UND-Verknüpfungen müssten fortgeführt werden bis


UND(E2>=$B$42;F2>=$C$42)

, was aber nicht möglich ist in Excel - hier geht es nur bis



UND(E2>=$B$32;F2>=$C$32)

- mehr Wahrheitsbedingungen kann eine ODER-Funktion nicht behandeln.
Wie kann ich dieses Problem lösen, ohne VBA zu nutzen?

Matrixformel

18.01.2012 12:03:54

Rudi

Hallo,

	B	C	D	E	F	G
1	Beginn	Ende
2	01.01.2012	30.05.2012		15.09.2015	01.11.2015	OK
3	01.09.2012	30.11.2012		05.08.2014	30.09.2014	nicht OK
4	01.01.2013	30.05.2013		15.02.2020	01.04.2020	OK
5	01.09.2013	30.11.2013		01.06.2017	30.09.2017	nicht OK
6	01.01.2014	30.05.2014
7	01.09.2014	30.11.2014
8	01.01.2015	30.05.2015
9	01.09.2015	30.11.2015
10	01.01.2016	30.05.2016
11	01.09.2016	30.11.2016
12	01.01.2017	30.05.2017
13	01.09.2017	30.11.2017
14	01.01.2018	30.05.2018
15	01.09.2018	30.11.2018
16	01.01.2019	30.05.2019
17	01.09.2019	30.11.2019
18	01.01.2020	30.05.2020
19	01.09.2020	30.11.2020

Zelle	Formel
G2	{=WENN(SUMME(($B$2:$B$100<=E2)*($C$2:$C$100>=F2));"OK";"nicht OK")}
G3	{=WENN(SUMME(($B$2:$B$100<=E3)*($C$2:$C$100>=F3));"OK";"nicht OK")}
G4	{=WENN(SUMME(($B$2:$B$100<=E4)*($C$2:$C$100>=F4));"OK";"nicht OK")}
G5	{=WENN(SUMME(($B$2:$B$100<=E5)*($C$2:$C$100>=F5));"OK";"nicht OK")}

Achtung, Matrixformel!

Die geschweiften Klammern{} nicht eingeben,

sondern die Zelle mit
Shift + Strg + Enter
verlassen statt Enter alleine.

Gruß
Rudi

AW: Matrixformel

18.01.2012 12:34:47

philG

Also das nenne ich mal einen tollen Ansatz, das hat mir sehr geholfen RIESEN Dank Rudi!!!

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um Zeiträume in Excel zu vergleichen, kannst Du die folgenden Schritte befolgen:

Daten eingeben: Trage Deine Start- und Enddaten in die Spalten B und C (z.B. für Halbjahre) und Deine Vergleichszeiträume in die Spalten E und F ein.
Formel erstellen: Nutze die folgende Formel, um zu überprüfen, ob die Vergleichszeiträume innerhalb der festgelegten Halbjahre liegen:
```
=WENN(ODER(UND(MIN(E2:F2)>=MIN($B$2:$C$2);MAX(E2:F2)<=MAX($B$2:$C$2));"OK";"nicht OK")
```
Formel anwenden: Ziehe die Formel nach unten, um sie auf alle Zeilen anzuwenden.
Matrixformel verwenden (optional): Bei mehr als 32 Vergleichszeiträumen kannst Du eine Matrixformel verwenden:
```
{=WENN(SUMME(($B$2:$B$100<=E2)*($C$2:$C$100>=F2));"OK";"nicht OK")}
```
Vergiss nicht, die Formel mit Shift + Strg + Enter zu bestätigen.

Häufige Fehler und Lösungen

Fehler: Die ODER-Verknüpfung funktioniert nicht über 32 Bedingungen.
- Lösung: Verwende eine Matrixformel, um mehrere Zeiträume zu vergleichen, wie oben beschrieben.
Fehler: Daten werden nicht korrekt als Datum erkannt.
- Lösung: Stelle sicher, dass die Zellen als Datumsformat formatiert sind.
Fehler: Unklarheiten bei der Eingabe der geschweiften Klammern.
- Lösung: Gib die geschweiften Klammern {} nicht manuell ein, sondern verlasse die Zelle mit Shift + Strg + Enter.

Alternative Methoden

VBA-Makros: Wenn Du mit VBA vertraut bist, kannst Du ein Makro erstellen, das die Zeiträume automatisch vergleicht.
Pivot-Tabellen: Nutze Pivot-Tabellen, um Daten zu aggregieren und Zeiträume zu vergleichen.
Filter: Setze Filter in Excel ein, um schnell die relevanten Zeiträume sichtbar zu machen.

Praktische Beispiele

Hier sind einige Beispiele, wie Du Zeiträume in Excel vergleichen kannst:

Beispiel 1: Die Zeiten für verschiedene Halbjahre
- Spalte B: 01.01.2020, 01.07.2020
- Spalte C: 30.06.2020, 31.12.2020
- Spalte E: 15.03.2020, 20.08.2020
- Spalte F: 15.04.2020, 30.09.2020
- Ergebnis in G: "OK" oder "nicht OK"
Beispiel 2: Vergleich für Urlaubstage
- Spalte B: 01.06.2021, 01.07.2021
- Spalte C: 30.06.2021, 31.08.2021
- Spalte E: 15.06.2021, 10.07.2021
- Spalte F: 20.06.2021, 15.08.2021
- Ergebnis in G: "OK" oder "nicht OK"

Tipps für Profis

Nutzung von Named Ranges: Verwende benannte Bereiche für Deine Zeiträume, um die Formeln übersichtlicher zu gestalten.
Datenüberprüfung: Implementiere Datenüberprüfungen, um sicherzustellen, dass nur gültige Daten eingegeben werden.
BedConditional Formatting: Nutze die bedingte Formatierung, um die Zellen, die "nicht OK" zurückgeben, hervorzuheben.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Zeiträume gleichzeitig vergleichen? Du kannst eine Matrixformel verwenden, um alle Zeiträume in einem Schritt zu vergleichen, anstatt ODER-Verknüpfungen zu nutzen.

2. Warum wird mein Ergebnis nicht als Datum erkannt? Stelle sicher, dass die Zellen als Datumsformat formatiert sind. Manchmal werden die Daten als Text gespeichert.

3. Wie kann ich die Formel auf eine große Datenmenge anwenden? Ziehen die Formel nach unten, um sie auf die gesamte Spalte anzuwenden, oder nutze eine Matrixformel, die auf mehrere Zeilen gleichzeitig angewendet werden kann.

Beliebteste Forumthreads (12 Monate)

Forumthread: Zeiträume vergleichen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zeiträume in Excel vergleichen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread