Umsatzverteilung auf Monate mit Jahreswechsel

[gsiberger]

Hi Gemeinde,

ich stehe vor einer Herausforderung und denke, dass ihr mir hier sicher weiterhelfen könnt... ;)

Das Ziel:
Der Umsatz von (Rechnungs)Positionen soll auf einzelne Monate (tag-genau) aufgeteilt werden.

Funktioniert auch prima - solange sich der Zeitraum im selben Jahr befindet;
wenn der "Lesitungszeitraum" aber über das aktuelle Jahr hinausgeht, funkioniert die Formel nicht mehr.

Aus einem anderen Forum habe ich die Formel im Anhang gefunden (leider weiß ich nicht mehr aus welchem);
ich habe keine Ahnung wie ich die Formel anpassen muss, damit es funktioniert.


Schon einmal vielen Dank im Voraus...

Lg

[Guschti]

Hallo

Da in den Zellen J2:W2 kein richtiges Datum steht, wird es auch kompliziert.
Dann vergleichst du nur die Jahre Spalte G mit Jahre Zeile 2

Ein einfacher Weg findest du im Anhang

Mfg Guschti

[gsiberger]

Hi Guschti,

ich weiß, aber ich brauche leider eine Tabelle, da irl die Tabelle täglich um ein paar hundert Zeilen wächst.
Die Tabelle füllt sich aus meheren Dateien, die ich via PowerQuery in dieser Tabelle ausgebe.

Aber dank deinem Input habe ich die Formel ein wenig umgebaut und jetzt funktioniert es tadellos.
(Datei im Anhang).

VIELEN VIELEN DANK!!
und viel Vergnügen auf der Piste... :15:

Lg Gsiberger


P.S.
Wenn du eine Idee/Lösungsvorschlag hast, wie man das mit Power Query umsetzen kann, wäre das sensationell.

[gsiberger]

Hi Guschti,

leider muss ich das Thema nochmal öffnen.

Die Formel von dir funktioniert leider nicht 100%ig.

Wenn bspw. das Startdatum nach dem Monatsersten liegt, wird dieser Monat nicht berücksichtigt.
Wenn Start- und Enddatum jeweils der Monatserste sind, wirft es auch einen Fehler aus.

Ich wäre dir sehr dankbar, wenn du dir das noch einmal ansehen könntest.

Vielen Dank im Voraus und allen zusammen einen guten Rutsch ins neue Jahr

Lg aus Wien
Gsiberger

[Guschti]

Hallo

Das hab ich mir fast gedacht. Es wäre so einfach gewesen, Beginn am 1. des Monats, End am Monatsende.
Wenn du jetzt aber noch nur Arbeitstage und die Feiertage ausschliessen willst, gebe ich auf.

Jetzt zur neuen (Monster)-Formel in Zelle i3

Code:

=WENN(UND(MONAT($G3)=MONAT(I$2);JAHR($G3)=JAHR(I$2);TAG($G3)<>TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*(MONATSENDE(I$2;0)-$G3+1);WENN(UND(MONAT($H3)=MONAT(I$2);JAHR($H3)=JAHR(I$2);TAG($H3)<>TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*($H3-I$2+1);WENN(ODER(I$2>$H3;I$2<$G3);0;$E3/(DATEDIF($G3;$H3;"D")+1)*(TAG(MONATSENDE(I$2;0))))))

da irl die Tabelle täglich um ein paar hundert Zeilen wächst.

Da fürchte ich, dass dies nicht die einzigen Formeln in der Mappe sind, und die Berechnungen immer langsamer werden, und das Volumen an MB zunimmt.

Alles Gute im 2018

Mfg Guschti

[gsiberger]

Hello,

vielen vielen Dank...
...und nee, keine Sorge, Wochentage reichen aus... ;)

Leider sind jetzt zwei andere Punkte aufgekommen; einen konnte ich selber anpassen, den anderen leider nicht...

Musste die Formel anpassen, da es ein Problem gab, wenn das Enddatum der Monatserste war:

Code:

Deine Formel:
=WENN(UND(MONAT($G3)=MONAT(I$2);JAHR($G3)=JAHR(I$2);TAG($G3)<>TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*(MONATSENDE(I$2;0)-$G3+1);WENN(UND(MONAT($H3)=MONAT(I$2);JAHR($H3)=JAHR(I$2);TAG($H3)<>TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*($H3-I$2+1);WENN(ODER(I$2>$H3;I$2<$G3);0;$E3/(DATEDIF($G3;$H3;"D")+1)*(TAG(MONATSENDE(I$2;0))))))

Angepasst:
=WENN(UND(MONAT($G3)=MONAT(I$2);JAHR($G3)=JAHR(I$2);TAG($G3)>=TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*(MONATSENDE(I$2;0)-$G3+1);WENN(UND(MONAT($H3)=MONAT(I$2);JAHR($H3)=JAHR(I$2);TAG($H3)>=TAG(I$2));$E3/(DATEDIF($G3;$H3;"D")+1)*($H3-I$2+1);WENN(ODER(I$2>$H3;I$2<$G3);0;$E3/(DATEDIF($G3;$H3;"D")+1)*(TAG(MONATSENDE(I$2;0))))))

Habe also diese beiden Teile von "<>" auf ">=" geändert:
TAG($G3)>=TAG(I$2)
TAG($H3)>=TAG(I$2)

Das andere Thema:
Wenn der Leistungszeitraum im selben Monat beginnt und endet, soll der Betrag in diesem ausgegeben werden => aktuell wird der Betrag mit 3,5 bzw. 3 multipliziert?

Ich habe versucht die Formel um ein "Wenn" zu erweitern, aber das funkt leider nicht.
Diese Formel ist nur in Zelle: i4 

Code:

=WENN(UND(MONAT(I$2)=MONAT($G4)=MONAT($H4);JAHR(I$2)=JAHR($G4)=JAHR($H4));$E4;WENN(UND(MONAT($G4)=MONAT(I$2);JAHR($G4)=JAHR(I$2);TAG($G4)>=TAG(I$2));$E4/(DATEDIF($G4;$H4;"D")+1)*(MONATSENDE(I$2;0)-$G4+1);WENN(UND(MONAT($H4)=MONAT(I$2);JAHR($H4)=JAHR(I$2);TAG($H4)>=TAG(I$2));$E4/(DATEDIF($G4;$H4;"D")+1)*($H4-I$2+1);WENN(ODER(I$2>$H4;I$2<$G4);0;$E4/(DATEDIF($G4;$H4;"D")+1)*(TAG(MONATSENDE(I$2;0)))))))

Vielleicht kannst du mir hier auch noch helfen?

Schon mal vielen Dank im Voraus


Nachtrag:
Der Fehler tritt nur dann auf, wenn das Enddatum NICHT der Monatsletzte ist.

[gsiberger]

Hello,

so, ich habe die Lösung gefunden:
http://www.ms-office-forum.net/forum/sho...p?t=275489&page=2 (Post #23 by lupo)


In Zelle i2:

Code:

=RUNDEN(MAX(;MIN(J$2;$H3+1)-MAX(I$2;$G3))/($H3+1-$G3)*$E3;2)

@Guschti:
Vielen vielen Dank für deinen Support!
Ich bin wirklich begeistert.

Lg aus Wien und alles Gute für 2018.
Gsiberger