Clever-Excel-Forum

Hallo liebes Forum, 
ich scheitere an einer Herausforderung in Excel. Leider hat mir die Suche nicht weiter geholfen, darum erstelle ich ein neues Thema. 
Ich habe ein Ferienhaus das sehr oft Freunde von mir nutzen. Die zahlen keine Miete, sondern nur die Betriebskosten des Hauses. Weil die Betriebskosten im Winter höher sind, kostet ein Tag im Ferienhaus von Oktober bis Ostern 10€, von Ostern bis Oktober aber nur 5€.
Für das Jahr 2020 ergeben sich dadurch drei Zeiträume:
Zeitraum 1: 01.01.2020 bis 12.04.2020 in diesem Zeitraum kostet ein Tag 10€
Zeitraum 2: 13.04.2020 bis 30.09.2020 in diesem Zeitraum kostet ein Tag 5€
Zeitraum 3: 01.10.2020 bis 31.12.2020 in diesem Zeitraum kostet ein Tag 10€

Jetzt möchte ich für jeden Gast im Ferienhaus eine Zeile erstellen, in die ich unter anderem Anreisedatum und Abreisedatum eingebe. Dann soll automatisch errechnet werden, wie viele Tage der Mensch im Haus ist und wie viele Tage in welchen der oben genannten Zeiträume fallen.
Ist also der "Beispielmensch" vom 06. bis 18.04.2020 im Haus, soll die Tabelle je in einer Spalte ausspucken: 14 (Aufenthaltstage gesamt, das bekomme ich noch selber mit DATEDIF hin); 7 (Aufenthaltstage Zeitraum 1); 6 (Aufenthaltstage Zeitraum 2); 0 (Aufenthaltstage Zeitraum 3) Damit die Tabelle die Werte für die Zeiträume 1-3 ausspuckt, bin ich auf der Suche nach einer möglichst einfachen Formel. Könnt Ihr mir hier helfen?

Ich hoffe ihr fühlt was ich denke! Schon mal vielen Dank für Eure Hilfe!
Grüße!

Hi,

schau mal, ob dir das schon weiterhilft: https://www.herber.de/excelformeln/pages...isten.html

Hallo B...,

in Excel steht die Zahl eines eingetragenen Datums eigentlich nicht für einem Zeitraum, sondern für einen Zeitpunkt um 00Uhr des eingetragenen Datums.

Wenn du die Werte so einträgst ist eine einfache Formel so (Anlage Spalte B):

=MAX(0;MIN(D6;$D$1)-MAX(C6;$C$1))*$B$1+MAX(0;MIN(D6;$D$2)-MAX(C6;$C$2))*$B$2+MAX(0;MIN(D6;$D$3)-MAX(C6;$C$3))*$B$3

In deiner Form der Zeiträume wäre die Formel so:

=MAX(0;MIN(D6+1;$D$1+1)-MAX(C6;$C$1))*$B$1+MAX(0;MIN(D6+1;$D$2+1)-MAX(C6;$C$2))*$B$2+MAX(0;MIN(D6+1;$D$3+1)-MAX(C6;$C$3))*$B$3

In den Spalten F:P habe ich die einzelnen Teile der Formel gesondert berechnet.

Hallo,

ich versuche einmal die Formel zur Berechnung der Schnittmenge zweier Intervalle "MAX(0;MIN(O1;O2)-MAX(U1;U2))" inWorte zu fassen:

1. Wenn zwei Intervalle eine nichtleere Schnittmenge haben, ist die Untergrenze der Schnittmenge das Maximum der Untergrenzen und die Obergrenze der Schnittmenge  das Minimum der Obergrenzen. Die Länge der Schnittmenge ist "=MIN(O1;O2)-MAX(U1;U2)".

2. Wenn zwei Intervalle keine nichtleere Schnittmenge haben ist das Minimum der Obergrenzen kleiner gleich dem Maximum der Untergrenze und somit ist die Differenz "=MIN(O1;O2)-MAX(U1;U2)" kleiner gleich 0.

3. Die Länge der Schnittmenge ist also die Differenz, falls sie größer als Null ist und ansonsten 0. "=MAX(0;Differenz)"

Hallo Blaubär,

ich habe eine kleine VBA-Funktion, die eine Schnittmenge zweier Zeiträume ermittelt. (s. Mappe)

Gruß
Sigi

Guten Morgen zusammen!
Vielen Dank für die zahlreichen Antworten! Ich habe heute Nachmittag etwas Luft, da schaue ich mir Eure Lösungen mal genauer an.
Einen guten Wochenstart!

So, konnte nun meine Tabelle mit Euren Lösungsvorschlägen ausstatten und mein Problem ist gelöst. Ich danke Euch wirklich sehr für Eure schnelle und kompetente Hilfe!
Beste Grüße!
Blaubär