Excel - Differenz zwischen Heute und letzter Donnerstag eines Monats?
Hallo, ich versuche derzeit eine kleine Tabelle aufzustellen für meine Finanzen. Dazu gehören auch Spielereien wie Geld/Tag zum ausgeben.
Ich erhalte mein Gehalt am letzten Donnerstag eines Monats und wollte daher ausrechnen, wie viel Geld ich am Tag übrig habe zwischen dem letzten Donnerstag im Monat und dem Mittwoch vor dem letzten Donnerstag im Monat. Es ist beispielsweise der 15.03.2017, somit verbleiben noch 15 Tage bis zum letzten Donnerstag im März 2017. Dass es nur noch 15 Tage sind sollte die Exceltabelle selbst erkennen eben anhand des aktuellen Datums und des letzten Donnerstags eines jeden Monats. Die Formel soll also das aktuelle Datum erkennen und das des letzten Donnerstags im aktuellen Monat und daraus die Differenz bilden (z. B. 15 Tage).
Habs schon stunden lang selbst versucht, komme einfach nicht drauf...
3 Antworten
Am besten ist, du legst dir als Namen eine Formel für den letzten Donnerstag eines Monats an. Weitere Rechnungen werden dann einfacher.
Die Formel könnte für den aktuellen Monat z. B. so aussehen:
=MONATSENDE(HEUTE();0)-(REST(WOCHENTAG(MONATSENDE(HEUTE();0))+2;7))
=SUMMENPRODUKT((MONATSENDE(HEUTE();0)+1-ZEILE($1:$7))*(REST(MONATSENDE(HEUTE();0)+1-ZEILE($1:$7);7)=5))
ergibt für Jan'17 Do 26.1.17 (Ben-def Format: TTT T.M.JJ)
oder (für beliebigen Monat, zB Februar 2017):
=SUMMENPRODUKT((MONATSENDE("1.2.17";0)+1-ZEILE($1:$7))*(REST(MONATSENDE("1.2.17";0)+1-ZEILE($1:$7);7)=5))
Heute() bzw "1.2.17" kann natürlich auch ein Bezug sein, dann muss man das Datum nur einmal (in eine andere Zelle) schreiben. (und dann alles ohne Gänsefüßchen). Auch ...;0) kann ein Bezug sein: ;0) =zitierter Monat, ;1) der danach, ;-1) der davor, ;12) derselbe ein Jahr danach etc.
Erklärung:- Rest(Datumszahl;7)=5 ist wahr für Donnerstag (Sa:0, So:1,..., Fr:6)
- Zeile($1:$7) generiert eine Laufzahl 1..7 (in Matrixformeln, und Summenprodukt ist eine Quasi-Matrixformel)
- Monatsende+1 ist der nächste 1., von dem 1..7 abgezogen wird.
- NUR bei der Laufzahl, wo Rest(..)=5 zutrifft (WAHR-->1 ist), wird diese Datumszahl *1 gerechnet, ansonsten *0. Da dies im Zeitraum von 7 Tagen nur einmal sein kann, ist das der richtige Donnerstag.
- Das Ganze lässt sich auch ,mit dem vorletzten Do machen, dann anstatt +1 -6 einsetzen.
- der erste Do im Monat wäre noch einfacher:
- =SUMMENPRODUKT(("1.1.17"-1+ZEILE($1:$7))*(REST("1.1.17"-1+ZEILE($1:$7);7)=5)) (ergibt 5.1.17)
- auch hier: der zweite Do im Monat entweder 1.1.17+6 oder einfacher gleich: "7.1.17" (Die Laufzahl fängt leider mit 1 an und nicht mit 0, man könnte natürlich auch bei der Laufzahl 1 abziehen und dann 1.1.17 oder 8.1.17 oder im Fall ganz oben Monatsletzter direkt verwenden, so wie wir das eher gewohnt sind)
Vllt ist die Formel etwas länger als manche andere, aber sie ist sehr anschaulich, wenn man sie mal vewrstanden hat!
Achso, die Differenz zu heute() kriegst Du dann wohl selber hin!
Hallo,
wenn ich dich richtig verstanden habe... in A1 den gewünschten Wochentag eintragen als Zahl 1-7 (1= Montag, 4 = Donnerstag)
=7*KÜRZEN(DATUM(JAHR(HEUTE());MONAT(HEUTE())+1;6-A1)/7)+A1-6-HEUTE()
LG, Chris
Ja perfekt! Hat genau richtig funktioniert vielen Dank!