Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1204to1208
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

verschachtelte Wenn-Formel

verschachtelte Wenn-Formel
Cornelia
Grüezi miteinander!
Ich suche Hilfe beim Erstellen einer Formel, in der es um Abgabefristen geht. Und zwar müssen mir Quartalsdaten geliefert werden, die jeweils am Ende des Folgemonats eingeliefert werden müssen; ausser beim Jahresende, da gibt’s 11/2 Monate Zeit. Konkret: die Daten sollen wie folgt geliefert werden
per 31.03. bis 30.04.
per 30.06. bis 31.07.
per 30.09. bis 31.10.
per 31.12. bis 15.02.
Diese Fristen werden mit der foglenden Formel berechnet, sobald der Kunde in Zelle A1 des Formulars erfasst, für welches Quartal er Zahlen liefert:
=WENN(MONAT(A1)=12;MONATSENDE(A1;1)+15;MONATSENDE(A1;1)) das nenne ich nun mal meine Hauptformel
Wenn nun aber die errechnete Einreichefrist auf einen Samstag oder Sonntag fällt, muss der Kunde die Zahlen erst am folgenden Montag einliefern.
Eigentlich müsste die Formel doch ungefähr so aussehen:
=wenn(wochentag(hauptformel)=7;hauptformel+2;wenn(wochentag(hauptformel)=1;hauptformel+1;hauptformel) )
Daraus habe ich diese Formel zusammengestellt:
=WENN(WOCHENTAG(WENN(MONAT(A1)=12;MONATSENDE(A1;1)+15;MONATSENDE(A1;1))=7;WENN(MONAT(A1)=12; MONATSENDE(A1;1)+15;MONATSENDE(A1;1))+2);WENN(WOCHENTAG(WENN(MONAT(A1)=12;MONATSENDE(A1;1)+15; MONATSENDE(A1;1))=1;WENN(MONAT(A1)=12;MONATSENDE(A1;1)+15;MONATSENDE(A1;1))+1);WENN(MONAT(A1)=12; MONATSENDE(A1;1)+15;MONATSENDE(A1;1))))
Aber ich schaffe es einfach nicht, die Formel korrekt zusammen zu setzen  Als Ergebnis erhalte ich die Fehlermeldung #ZAHL!: kann mir jemand helfen, den Fehler zu finden?
Herzlichen Dank im Voraus!
coko

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: verschachtelte Wenn-Formel
17.03.2011 17:30:47
Bernd
Hallo Cornelia,
habe Dir mal eine Beispieldatei hochgeladen:
https://www.herber.de/bbs/user/74000.xls
Die Formel kann man bestimmt noch etwas schlanker gestalten, aber auf die Schnelle sollte es auch so funktionieren :-)
Gruß Bernd
AW: verschachtelte Wenn-Formel
17.03.2011 18:35:23
IngGi
Hallo zusammen,
eine kürzere Variante wäre zum Beispiel
=WENN(MONAT(A5)=12;MONATSENDE(A5;1)+15;MONATSENDE(A5;1))+SVERWEIS(WOCHENTAG(WENN(MONAT(A5)=12;MONATSENDE(A5;1)+15;MONATSENDE(A5;1));2);{1.0;2.0;3.0;4.0;5.0;6.2;7.1};2;0)
Gruß Ingolf
AW: verschachtelte Wenn-Formel
17.03.2011 18:44:56
Bernd
Hallo Ingolf,
das ist ja mal eine geschmeidige Formel ... HUT AB! Wäre ich nie drauf gekommen und wandert deshalb auch in meine Formelsammlung.
@ Cornelia
Nimm die Formellösung von Ingolf
@ Christian
Deine Formel funktioniert nicht ganz richtig. Teste mal beim Datum 31.12.2013: Ergebnis Deiner Formel 15.02.2014. Richtig wäre 17.02.2014
Gruß Bernd
Anzeige
AW: verschachtelte Wenn-Formel
17.03.2011 18:22:38
Christian
Hallo Coko,
mein Vorschlag:
=MONATSENDE(A1;1)+(8-WOCHENTAG(MONATSENDE(A1;1);2))*(WOCHENTAG(MONATSENDE(A1;1);2)>5) +15*(MONAT(A1)=12)
Gruß
Christian
AW: verschachtelte Wenn-Formel
17.03.2011 18:42:53
Christian
..noch 'ne Korrektur, hatte übersehen, dass auch für die 1,5 Monatsfrist die Wochenenden berüchsichtigt werden sollen:
=MONATSENDE(A5;1)+(8-WOCHENTAG(MONATSENDE(A5;1);2))*(WOCHENTAG(MONATSENDE(A5;1);2)>5) +(15+(8-WOCHENTAG(MONATSENDE(A5;1)+15;2))*(WOCHENTAG(MONATSENDE(A5;1)+15;2)>5))*(MONAT(A5)=12)
Somit ist die Formel von Ingolf kürzer
Gruß
Christian
AW: verschachtelte Wenn-Formel
17.03.2011 18:48:23
Bernd
Hallo Christian,
mein Eintrag hatte sich mit Deinem überschnitten. Auch wenn die Formel länger ist als die von Ingolf finde ich die Lösung auf jeden Fall besser als meine. Man lernt nie aus und erkennt, dass es zu einer Problemstellung immer mehrere Lösungsansätze gibt.
Gruß Bernd
Anzeige
naja, dann aber besser so...
17.03.2011 19:09:34
Christian
...
=MONATSENDE(A5;1)+15*(MONAT(A5)=12)+(8-(WOCHENTAG(MONATSENDE(A5;1)+15*(MONAT(A5)=12);2))) *(WOCHENTAG(MONATSENDE(A5;1)+15*(MONAT(A5)=12);2)>5)
Gruß
Christian
AW: verschachtelte Wenn-Formel
18.03.2011 10:43:54
Cornelia
hallo Bernd, Ingolf und Christian!
Obwohl ich erst jetzt dazu komme, mich wieder meiner Formel zu widmen, danke ich euch ganz herzliche: ihr seid einfach phänomenal!
Ich werde natürlich alle Vorschläge ausprobieren und vorallem zu verstehen versuchen; aber um im Projekt weiterzukommen, werde ich mich wohl für die kürzeste Formel d.h. die von Ingolf entscheiden. Für die schnelle Hilfe bin ich wirklich sehr froh :-)
Herzliche Grüsse
coko
Anzeige
einen Monat nach Quartal ohne WoEnde
18.03.2011 23:00:16
Erich
Hi zusammen,
hier mal die Formeln zusammen und noch ein Vorschlag in Spalte E:
 ABCDE
4Daten perAbgabeterminIngolfChristianErich
531.03.201102.05.201102.05.201102.05.201102.05.2011
630.06.201101.08.201101.08.201101.08.201101.08.2011
730.09.201131.10.201131.10.201131.10.201131.10.2011
831.12.201115.02.201215.02.201215.02.201215.02.2012
931.12.201317.02.201417.02.201417.02.201417.02.2014
10     
1122.10.201331.12.201302.12.201302.12.201317.02.2014
1201.07.201130.09.201131.08.201131.08.201131.10.2011

Formeln der Tabelle
ZelleFormel
B5=WENN(UND(MONAT($A5)=12;WOCHENTAG(MONATSENDE($A5;1)+15;2)=6); MONATSENDE($A5;1)+17;WENN(UND(MONAT($A5)=12;WOCHENTAG(MONATSENDE($A5;1)+15;2)=7); MONATSENDE($A5;1)+16;WENN(UND(MONAT($A5)=12;WOCHENTAG(MONATSENDE($A5;1)+15;2)<6); MONATSENDE($A5;1)+15;WENN(UND(MONAT($A5)<>12;WOCHENTAG(MONATSENDE($A5;1); 2)=6); MONATSENDE($A5;1)+2;WENN(UND(MONAT($A5)<>12;WOCHENTAG(MONATSENDE($A5;1); 2)=7); MONATSENDE($A5;1)+1;MONATSENDE($A5;1))))))
C5=WENN(MONAT(A5)=12;MONATSENDE(A5;1)+15;MONATSENDE(A5;1))+SVERWEIS(WOCHENTAG(WENN(MONAT(A5)=12;MONATSENDE(A5;1)+15;MONATSENDE(A5;1)); 2); {1.0;2.0;3.0;4.0;5.0;6.2;7.1};2;0)
D5=MONATSENDE(A5;1)+15*(MONAT(A5)=12)+(8-(WOCHENTAG(MONATSENDE(A5;1)+15*(MONAT(A5)=12); 2))) *(WOCHENTAG(MONATSENDE(A5;1)+15*(MONAT(A5)=12); 2)>5)
E5=DATUM(JAHR(A5); 3*MONAT(MONAT(A5)&0)+2;)+15*(MONAT(A5)>9) +MAX(;2-REST(DATUM(JAHR(A5); 3*MONAT(MONAT(A5)&0)+2;)+15*(MONAT(A5)>9); 7))

Bei den oberen Beispielen liefern alle Formeln den gleichen Wert.
In den Zeilen 11:12 wird in Spalte E das gewünschte Datum auch dann berechnet, wenn Spalte A kein Quartalsende ist.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige