Bei der Terminplanung ist es wichtig zu überprüfen, ob sich zwei Zeiträume überschneiden. Dazu gibt es eine einfache Lösung, wie du in Excel überschneidende Zeiträume findest.

Vor allem in der Terminplanung oder im Projektmanagement können Probleme entstehen, wenn sich Zeiträume überschneiden. Diese kannst du bereits in der Planung vermeiden, indem du deine Termine auf Überschneidungen prüfst.

Wie immer habe ich für dich eine Übungsdatei erstellt, die du dir kostenlos herunterladen kannst, wenn du diesem Link folgst: Übungsdatei überschneidende Zeiträume

Mit dieser Formel überprüfst du, ob sich zwei Zeiträume überschneiden

Im Netz findest du verschiedene Varianten, um zu überprüfen, ob sich zwei Zeiträume überschneiden.

Von verketteten WENN-Funktionen über ZÄHLENWENNS-Formeln bis hin zu SUMMENPRODUKT-Formeln ist alles dabei.

Ich möchte dir die meiner Meinung nach einfachste Möglichkeit vorstellen. Dazu verwende ich die SUMMENPRODUKT-Funktion.

Ziel ist es herauszufinden, ob sich zwei unterschiedliche Zeiträume überschneiden. Grafisch dargestellt also so:

Dazu musst du parallel überprüfen, ob entweder der Anfang des ersten Zeitraums, das Ende oder beides innerhalb des zweiten Zeitraums liegt.

Folgende Formel kannst du nutzen, um zu prüfen, ob sich zwei Zeiträume überschneiden

=SUMMENPRODUKT((A1<= E2)*(E1 >= A2))

Hier machst du dir die Tatsache zunutze, dass jedes Datum in Excel eine fortlaufende Zahl ist. Beispielhaft die Zahl hinter dem 01.01.2021(das ist die 44197) kleiner als die Zahl hinter dem 02.01.2021(das ist die 44198) ist.

Wenn du zum ersten Mal mit Datums oder Zeitwerten in Excel arbeitest, kann ich dir meinen Artikel Nie wieder zu wenig Zeit: Excel DATUM und UHRZEIT Funktionen einfach erklärt dazu empfehlen, dort lernst du alles Wissenswerte.

Um die Funktionsweise der Formeln besser zu verstehen, gelten für die folgenden Formeln diese Abkürzungen:

A1 = Anfang erster Zeitraum

E1 = Ende erster Zeitraum

A2 = Anfang zweiter Zeitraum

E2 = Ende zweiter Zeitraum

Die SUMMENPRODUKTFunktion prüft im ersten Argument ob A1 kleiner oder gleich E2 ist. Und im zweiten Argument ob E1 größer oder gleich A2 ist. Sind beide Argumente WAHR, gibt die Formel eine 1 aus, wenn nicht, dann eine 0.

Kontrolle, ob die Formel in allen möglichen Kombinationen funktioniert

Als Kontrolle, ob die Formel auch wirklich funktioniert, setze ich diese einfach in allen möglichen Kombinationen ein.

Ich denke, die Funktionsweise dahinter ist klar. Falls nicht, Kontaktiere mich einfach und ich helfe dir. Du kannst mit hier erreichen.

Alternative zur SUMMENPRODUKT Funktion

Als Alternative kannst du auch die ZÄHLENWENNS-Formel einsetzen, die nicht viel komplexer ist.

=ZÄHLENWENNS(A1;“<=“&E2;E1;“>=“&A2)

Du musst lediglich darauf achten, dass du logische Operatoren also > oder < in ““ setzt.

Willst du wirklich nur zwei Zeiträume miteinander vergleichen, dann kannst du eine noch einfachere Formel verwenden nämlich diese.

=(A1<= E2)*(E1 >= A2)

Erst wenn du einen Zeitraum mit mehreren anderen vergleichen willst, dann ist die Funktion SUMMENPRODUKT oder ZÄHLENWENNS erforderlich.

Das ist auch schon der ganze Zauber.

Mit dieser eigentlich einfachen Formel kannst du beliebige Zeiträume auf Überschneidungen prüfen. Wenn du die Formel noch etwas verpackst, erhältst du auch eine verständliche Ausgabe.

Als Nächstes werfen wir einen Blick auf eine Formel, die einen Zeitraum mit mehreren weiteren abgleicht.

Prüfen, ob sich mehrere Zeiträume überschneiden

Wie vorhin erwähnt, ist jetzt die SUMMENPRODUKT oder ZÄHLENWENNS Funktion zwingend erforderlich.

Allerdings ist die Formel jetzt nicht wesentlich komplizierter, es ändert sich nur der Bezug, mit dem wir die Anfangs- und Endwerte des Zeitraums vergleichen.

Als Beispiel kann die Formel wie in folgendem Bild aussehen, dabei ist wieder nur die SUMMENPRODUKT-Funktion wichtig, die WENN-Funktion drum herum sorgt nur für eine nutzerfreundliche Ausgabe.

So berechnest du die Länge der Überschneidungen

Eine Anforderung könnte auch lauten, die Länge der Überschneidungen festzustellen.

Hier wird es schon etwas komplizierter, da ich mit der Formel alle Möglichkeiten abdecken möchte. Also ob der Ausganszeitraum völlig in den anderen Zeiträumen enthalten ist, ob er zwischen Anfang und Ende des zweiten Zeitraums beginnt oder ob er dazwischen endet.

Dazu sind einige WENN-Schleifen notwendig. Aber einmal verstanden sind diese leicht zu durchschauen.

Jede WENN-Schleife überprüft, ob eine Möglichkeit zutrifft und berechnet anschließend die Differenz. Wenn nicht, prüft es auf die nächste Möglichkeit und so weiter.

Jede Schleife berechnet die Differenz dann aus einer anderen Basis.

Nimm dir die Zeit und lerne die Formel kennen, dann ist es für dich ein leichtes diese auf deine spezielle Situation anzupassen.

Wie du eine Formel richtig analysierst und verstehst ist für jeden Excel Nutzer der Formeln erstellt essenziell. Daher habe ich dir in dem sehr ausführlichen Artikel So gelingt dir jede Formel Analyse alles erklärt, was du dazu brauchst.

Ich habe dir zwei unterschiedliche Varianten der Berechnung erstellt, einmal die Variante, wo Tagesdifferenzen berechnet werden und einmal wo die Differenz auf Minuten genau berechnet wird.

Warum sind die Formeln unterschiedlich?

Einziger Unterschied der beiden Formeln ist das +1 in der Tages Formel und +(1/24/60) in der Minuten Formel.

Das hat folgenden Grund.

Wie oben beschrieben sind Datums und Zeitwerte nichts anderes als Zahlen.

Nehmen wir als Beispiel eine Überschneidung der Zeiträume 13.01 bis 17.01 und 15.01 bis 19.01 die Formel ohne +1 würde ein Ergebnis von 2 liefern.

Das ist jedoch falsch, da sich der 15.01 in beiden Zeiträumen befindet, der 16.01 und der 17.01. Ich nehme dabei an, dass die Zeiträume immer von 00:00 Uhr bis 23:59 reichen.

Daher muss in der Formel das +1 ergänzt werden, um auf den meiner Meinung nach richtigen Wert zu kommen. Oder eben +(1/24/60) was einer Minute entspricht.

ACHTUNG: Das ist meine persönliche Meinung. Wenn deine Zeiträume immer zur selben Zeit beginnen und enden, dann ist die Berechnung in Excel ohne Zusatz korrekt.

So berechnest du die Anzahl, wenn sich Zeiträume überschneiden

Nun geht es nicht darum, herauszufinden wie groß die Überschneidung ist, sondern darum, herauszufinden wie viele Zeiträume sich teilweise oder ganz überschneiden.

Das kannst du vor allem in der Urlaubsplanung hervorragend einsetzen, da du damit berechnen kannst, wie viele Mitarbeiter in einem bestimmten Zeitraum oder an einem bestimmten Tag im Urlaub sind.

Die Formel bleibt exakt dieselbe. Denn die SUMMENPRODUKT-Funktion berechnet ohnehin die Anzahl der Überschneidungen.

Wenn du dir die Formel mal im Detail untersuchst, erhältst du folgendes Ergebnis:

1 Schritt

Das erste Array der SUMMENPRODUKT-Funktion, überprüft alle Werte aus der Spalte mit den Endwerten, mit dem jeweiligen Anfangswert und gibt das entsprechende Array zurück.

2 Schritt

Nun wird das zweite Array auf dieselbe Weise berechnet.

3 Schritt

Im letzten Schritt werden die Arrays miteinander multipliziert und anschließen die Summe berechnet.

Wie auch du eine Formelanalyse auf diese Weise durchführen kannst, zeige ich dir in diesem Artikel So gelingt dir jede Formel Analyse.

Oder wenn du mehr über die Funktion SUMMENPRODUKT erfahren möchtest, dann ist der Artikel An alle Wissbegierigen: Die Excel Funktion SUMMENPRODUKT einfach erklärt bestimmt der richtige für dich.

Fazit

Festzustellen, ob sich Zeiträume überschneiden und wenn ja um wie viel ist mit dem Einsatz der richtigen Formel sehr einfach.

Neben den hier gezeigten Beispielen gibt es wahrscheinlich noch dutzende andere Lösungswege und Anwendungen. Das zeigt mir wieder einmal, wie vielfältig Excel eingesetzt werden kann.

Im nächsten Artikel möchte ich dir daher die Formeln in einer praktischen Anwendung zeigen, nämlich in der Erstellung eines Terminplaners.

Hier ein kleiner Vorgeschmack auf meinen nächsten Artikel:

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor Excel der smarte Excel Problem Löser

PS.: Wenn der Artikel hilfreich für dich war, dann teile ihn doch gerne mit anderen, denen er auch helfen könnte.

Doktor Excel

Doktor Excel

Heißt eigentlich Michael und arbeitet seit über 10 Jahren privat und beruflich mit Excel. Er hat dabei viele Zeitsparende Arbeitsweisen kennen gelernt und teilweise selbst entwickelt.

Hier findest du seine Geschichte: Wer ist dieser Doktor Excel und was hat ein Arzt mit Excel zu tun?

Du kannst ihm auch jederzeit über die Kontakseite eine Nachricht zukommen lassen.