Wenn(und( mit Summenprodukt?

[BoskoBiati]

Hi,

ich habe mich an die Tabelle in #1 gehalten. Die Formel in #6 scheint mir fehlerhaft. Ich würde diese vorschlagen:

Code:

=WENNFEHLER(SVERWEIS(A2;$J$2:$L$16;3;0);WENN((REST(A2;7)<2);TEXT(A2;"TTT")&WENN((A2>=$J$44)*(A2<=$K$44);" Som";" Win");WENN(SUMMENPRODUKT(($J$20:$J$29<=A2)*($K$20:$K$29>=A2));"F";"S")))

Die letzte Formel ist nicht nachvollziehbar, da die Bezüge zu O32:P53 nicht ersichtlich sind, da die Zellen in der vorliegenden Tabelle leer sind. Sie ließe sich aber mit Sicherheit kürzen.

[ari-2001]

  Kalender Beispiel.xlsx (Größe: 48,61 KB / Downloads: 4) Huhuuuu,

ich habe den Kalender einmal beigefügt, die Formel steht in Zelle D2.

Vielleicht kann man die ja wirklich noch vernünftig kürzen, so ist es schon etwas unübersichtlich, aber alles funzt so, wie ich es gerne hätte.
Und es werden mit dieser einen Formel alle 35 Betriebstagsgruppen, die ich benötige, (stehen in t4 bis u36, werden aber nicht in der Formel benötigt)
generiert.

Vielen Dank für die Mithilfe.

Viele Grüße
Andreas

[Rudi'S]

Hi Andreas,

ohne mir die Formel näher anzusehen, abgesehen von evtl. Kürzungen die die Profis noch vorschlagen werden, ermittelst du doch schon in den Spalten G und H die Ferientage. Damit kannst du doch den Bezug auf / für diese Tage in Spalte D verwenden.
Ansonsten aber Glückwunsch.

Gruß Rudi

[ari-2001]

Hallo Rudi,

wenn es mal so einfach wäre.

Ich brauchte folgende Tage

01.) Montag - (S)chule Nordrheinwestfalen / (S)chule Niedersachsen (S/S)
02. )Dienstag S/S
03.) Mittwoch S/S
04.) Donnerstag S/S
05.) Freitag S/S
06.) erster Freitag im Monat S/S (an jedem ersten Freitag im Monat gibt's es Besonderheiten, außer es ist ein Feiertag)

dann

07.) Montag (S)chule NRW / (F)erien NDS (S/F)
08.) Dienstag S/F
09.) Mittwoch S/F
10.) Donnerstag S/F
11.) Freitag S/F
12.) erster Freitag im Monat S/F

dann

13.) Montag (F)erien NRW / (S)chule NDS (F/S)
14.) Dienstag F/S
15.) Mittwoch F/S
16.) Donnerstag F/S
17.) Freitag F/S
18.) erster Freitag im Monat F/S


dann

19.) Montag (F)erien NRW / (Ferien) NDS (F/F)
20.) Dienstag F/F
21.) Mittwoch F/F
22.) Donnerstag F/F
23.) Freitag F/F
24.) erster Freitag im Monat F/F


Dann gibt es verschiedene Samstage und Sonntage:

25.) Samstags Win (Sa Win)
26.) Sonntags Win (So Win)
 - vom 01.01. eines jeden Jahres bis zum Karfreitag (in diesem Jahr 10.04.) und ab dem ersten Samstag nach Allerheiligen bis zum Ende des Jahres ( daher die Zeitspanne in o53 und p53  )

an den restlichen Samstagen und Sonntage (in der Zeit von Karfreitag bis Allerheiligen) müssen diese Tage einfach

27.) Sa
28.) So

heißen.

Dann kommen die Feiertage dazu

29.) FTG WIN
 - alle Feiertage bis zum Karfreitag und alle Feiertage nach Allerheilgen heißen FTG Win
 - die FTG Win, die auf einem Sonntag fallen, heißen aber nicht FTG Win, sondern So Win (warum auch immer, ist eben so)
30.) FTG
 - alle Feiertage, die nach Karfreitag bis Allerheiligen stattfinden, heißen FTG
 - die Feiertage, die auf einem Sonntag fallen, heißen nicht FTG sondern So
Ausnahme:

31.) Do SoV4 (Fronleichnam)
 - Feiertag in NRW, aber in NDS entweder DO S/S, Do S/F, Do F/F oder Do F/F, je nachdem welcher Betriebstag grade ist.

Dann gibt es noch

32.) Heil (Heiligabend)
33.) Sil (Silvester)
34.) Heil So (Heiligabend auf einem Sonntag)
35.) Sil So (Silvester auf einem Sonntag)


Daraus ergeben sich also einige Möglichkeiten, die die Formel auf jeden Fall ausrechnen kann
Am schlimmsten war für mich die Formel für den ersten Freitag im Monat, nach dem ich die aber
zusammenhatte und Rudi und Elex mir auch geholfen haben, bin ich zumindest erst einmal soweit
gekommen, dass die Formel für Nordrheinwestfalen super arbeitet.

Ach ja, ich habe die gleiche Formel noch für  Spalte E (Niedersachsen) vor mir, aber das mache ich am Samstag, im
Moment bin ich erst einmal bedient.

Im Grunde ändern sich die Betriebstage in Niedersachsen nur an den nicht bundeseinheitlichen Feiertagen.

Die Frage, warum das so kompliziert ist, kann ich nur damit beantworten, dass ich alle Betriebstagsvarianten für
Abrechnungen benötige.


Aber bisher klappt alles bestens, auch von Raoul21 bekomme ich viel Hilfe, der mir unermüdlich hilft.

In diesem Sinne noch einmal vielen herzlichen Dank an alle!

Viele Grüße
Andreas

[BoskoBiati]

Hi,

so sollte es auch reichen:

Code:

=WENN((A2=$O$4)+(A2=$O$11)*(REST(A2;7)=1);TEXT(A2;"TTT");WENN((A2=--("31.12."&$N$1))*(REST(A2;7)=2);"Heil So";WENN((A2=--("24.12."&$N$1))*(REST(A2;7)=2);"Sil So";WENNFEHLER(SVERWEIS(A2;$O$2:$Q$15;3;0);WENN((REST(A2;7)<2)*(A2<>$O$4)*(A2<>$O$11);TEXT(A2;"TTT")&WENN((A2>=$O$53)*(A2<=$P$53);"";" Win");WENN((F2="")*(TAG(A2)<8)*(REST(A2;7)=6);"1. Fr ";TEXT(A2;"TTT "))&WENN(SUMMENPRODUKT(($O$32:$O$39<=A2)*($P$32:$P$39>=A2));"F";"S")&"/"&WENN(SUMMENPRODUKT(($O$42:$O$51<=A2)*($P$42:$P$51>=A2));"F";"S"))))))

[ari-2001]

Hallo Edgar,

so weit, so gut, nur so ganz passt es nicht. Zum Beispiel werden Heil So und Sil So nicht korrekt dargestellt, wenn der Kalender zum Beispiel auf 2023 gestellt wird. Da der 01.01 auch auf einem Sonntag fällt, wird der Tag nicht als So Win (wie es bei meiner Formel klappt) und der 24.12. und 31.12 werden auch nicht mit dem Zusatz "So" dargestellt. Meine Formel ist zwar um Längen länger, aber die Formel stellt es einwandfrei da. Trotzdem vielen lieben Dank für deine Bemühungen.

Ich habe auch echt tagelang an meiner Formel gearbeitet, bis es endlich funktionierte.

Ich poste in Kürze noch die Formel für Niedersachsen

cu,

Andreas

[BoskoBiati]

Hi,

nochmal überarbeitet:

Code:

=WENNFEHLER(WENN(ISTZAHL(VERGLEICH(A2;$O$2:$O$13;0))*(REST(A2;7)<>1);SVERWEIS(A2;$O$2:$Q$13;3;0);WENN(ISTZAHL(VERGLEICH(A2;$O$14:$O$15;0));SVERWEIS(A2;$O$14:$Q$15;3;0)&WENN(REST(A2;7)=1;" So";"");WENN(REST(A2;7)<2;TEXT(A2;"TTT")&WENN((A2>=$O$3)*(A2<=$O$11);"";" Win");WENN((REST(A2;7)=6)*(TAG(A2)<8);"1. ";"")&TEXT(A2;"TTT ")&WENN(SUMMENPRODUKT(($O$32:$O$37<=A2)*($P$32:$P$37>=A2));"F";"S")&"/"&WENN(SUMMENPRODUKT(($O$42:$O$49<=A2)*($P$42:$P$49>=A2));"F";"S"))));"")

Für NDS:

Code:

=WENNFEHLER(WENN(ISTZAHL(VERGLEICH(A2;$O$18:$O$30;0))*(REST(A2;7)<>1);SVERWEIS(A2;$O$18:$O$30;3;0);WENN(ISTZAHL(VERGLEICH(A2;$O$29:$O$30;0));SVERWEIS(A2;$O$29:$O$30;3;0)&WENN(REST(A2;7)=1;" So";"");WENN(REST(A2;7)<2;TEXT(A2;"TTT")&WENN((A2>=$O$3)*(A2<=$O$11);"";" Win");WENN((REST(A2;7)=6)*(TAG(A2)<8);"1. ";"")&TEXT(A2;"TTT ")&WENN(SUMMENPRODUKT(($O$32:$O$37<=A2)*($P$32:$P$37>=A2));"F";"S")&"/"&WENN(SUMMENPRODUKT(($O$42:$O$49<=A2)*($P$42:$P$49>=A2));"F";"S"))));"")

Ggf. musst Du noch die Summenprodukte vertauschen.

[ari-2001]

Hallo Edgar,

ich bin sprachlos.
Deine Formel funktioniert ja genau wie meine, nur dass deine bedeutend kürzer ist.
Unglaublich.

Und wenn ich die Schritte nachvollziehe, was deine Formel macht, bin ich echt baff, dass gleiche wie meine aber alles viel komprimierter.

Tausend Dank, ich wäre im Leben nicht darauf gekommen, es so zu machen wie Du es machst, aber ich bin ja auch kein Excel-Profi.

Einen schönen Feiertag wünsche ich Dir.

Andreas