☰

Wie erhalte ich die Anzahl der Monate, die sich zwischen einem Datumspaar und einem beliebigen Zeitraum zwischen Juni und November eines beliebigen Jahres überschneiden?

tag-icon • tag-icon microsoft-excel worksheet-function
Wie erhalte ich die Anzahl der Monate, die sich zwischen einem Datumspaar und einem beliebigen Zeitraum zwischen Juni und November eines beliebigen Jahres überschneiden?

Bereich1 ist 01.06.2021-30.11.2021 und Bereich2 ist 15.08.2021-01.03.2022 (Bereich2 könnte jeder andere Datumsbereich sein). Ich möchte sehen, wie viele Monate von Bereich2 sich mit Bereich1 überschneiden. Die Antwort sollte 4 sein, da sich Nov, Okt, Sep und ein Teil von Aug mit Bereich2 überschneiden. Und in meinem Problem würde ich sagen, selbst wenn es 3 Monate und 3 Tage wären, 4 volle Monate. Allerdings zähle ich den ersten Tag des Monats nicht als vollen Monat.

Beispielsweise ist Bereich1 06.01.2021 - 11.01.2021 und Bereich2 02.01.2021 - 10.01.2021. Dann müsste die Antwort 4 sein, da sich Juni, Juli, August und September mit Bereich1 überschneiden. Beachten Sie, dass ich den 10.01.2021 nicht mitzähle, also könnte die Antwort nicht 5 sein.

Dies wurde bereits von einem hilfreichen Benutzer gelöstScott Craner

Die von uns verwendete Formel lautet: =COUNT(UNIQUE(FILTER(MONTH(SEQUENCE(P60-P59,,P59)),ISNUMBER(MATCH(SEQUENCE(P60-P59,,P59),SEQUENCE(E81-E80,,E80),0)),""))))

Es funktioniert einwandfrei, aber ich brauche es jetzt, um eine Reihe von Daten wie 01.02.2021 - 01.10.2021 und JEDEN Zeitraum von Juni bis November eines JEGLICHEN Jahres zu vergleichen. In der derzeit verwendeten Formel funktioniert es nicht, um überlappende Monate zwischen 01.02.2022 und 01.07.2022 zu finden, da sich 01.02.2022 - 01.07.2022 nicht mit 01.06.2021 - 30.11.2021 überschneidet. Kann ich die Anzahl der übereinstimmenden Monate zwischen einer beliebigen Reihe von Daten und einem beliebigen Zeitraum zwischen 01.06.## und 30.11.## eines beliebigen Jahres finden?

Antwort1

Ich werde es versuchen ... mein Ansatz zum Ermitteln der Überlappung für beliebige Sätze von zwei Datumsbereichen:

  1. Konvertieren Sie die Datumsbereiche a - b und c - d in die Anzahl der Monate seit 1900
  2. Nennen wir diese Monatszahlen a' - b' und c' - d'
  3. Also a' =(YEAR(a)-1900)*12 + MONTH(a)zB mit a=1/1/2020 dann a'=1441
  4. Um den 1. Tag eines jeden Monats zu ignorieren, addieren Sie -(DAY(a)<2)zur obigen Formel
  5. Unter der Annahme, dass b' > a' und d' > c' ist, beträgt die Überlappung MIN(d',b')-MAX(c',a')+1
  6. Wenn das obige Ergebnis negativ ist, gibt es keine Überlappung

Beachten Sie, dass ich zur Vereinfachung der Mathematik algebraische Variablen anstelle von Zelladressen verwende.

Ps. Wenn Sie sich in Excel austoben möchten, verwenden Sie den Intersect-Operator (ein Leerzeichen), um Spaltenbereiche (die die Monate seit 1900 darstellen) in einer leeren Spalte zu überschneiden, und zählen Sie dann die Anzahl der Leerzeichen, was z. B. =COUNTBLANK(Z1441:Z1446 Z1443:Z1447)4 ergibt. Dies könnte für mehrere Datumsbereiche sehr nützlich sein, da Sie eine beliebige Anzahl von Excel-Bereichen überschneiden können. Um den Datumsbereich der Monatszählung (a' bis b') in einen Zeilenbereich umzuwandeln, verwenden Sie, =INDEX(Z:Z,a'):INDEX(Z:Z,b')sodass die Überschneidungsanzahl schließlich beträgt =COUNTBLANK(INDEX(Z:Z,a'):INDEX(Z:Z,b') INDEX(Z:Z,c'):INDEX(Z:Z,d')).

verwandte Informationen