Source: http://sternenhimmelstuermer.eu/pctipps/excelparagrafen.html
Timestamp: 2017-08-21 12:09:04+00:00

Document:
excelparagrafen
82. Verexcelung von Paragrafen
Der Sternenhimmelstuermer führt trotz schmal bemessener Zeit über einen Rechtskanal. Dieser wird nun um einige Paragrafen ergänzt, deren Berechnungen mit Excel erfolgen. Mit Word ohne Makros bzw. VBA stößt auch der Sternenhimmelstuermer an seine Grenzen. Aber mit Formeln in Excel lassen sich die Berechnungen durchführen und dann mit der Software Exceleverywhere in Javascript/html umwandeln. Der Sternenhimmelstuermer wird die eben genannte Software/Plugin noch eingehend vorstellen. Vorerst sei nur angemerkt, dass je nach Version interaktive Exceltabellen in Flash oder Javascript/html erstellt werden können - und das quasi ohne Html-Kenntnisse.
Das kostet zwar etwas, aber für Excelenthusiasten ein muss ohne gleichwertige Alternativen der Freewarebewegung.
Doch zurück zu Excel. Der Sternenhimmelstuermer erstellte mal eine voll lauffähige Demobetabelle in Excel 2007 mit dem § 622 BGB, der fast alle Hürden beinhaltet, die so bei der Berechnung in Excel auftreten können. Die notwendigen Formeln werden dann auseinanderklamüsert. Der Schwierigkeitsgrad ist fortgeschrittener User (mindestens Grundlagen über Formeleingabe sollten vorhanden sein). Andere Abhandlungen auf dieser Homepage sind für Anfänger besser geeignet, aber aus Rücksicht auf fortgeschrittene User wird auf viele Details verzichtet.
Erklärung der Formeln anhand der Demotabelle
Formeln für Paragrafen und Eigenentwicklung zur Berechnung vom Quatal mit der Formel Obergrenze!
Die Demotabelle wurde in Paragrafenform mit Erklärungen gestaltet, so dass Sie auch für Anfänger verständlich ist. Grundlage ist der Paragraf § 622 BGB:
Der blaue Text sind die erstmal die erwünschten Ergebnisse
Der rote Text ergibt sich aus einem Gerichtsurteil, wonach die Beschäftigungszeiten vor dem 25. Lebensjahr wegen Diskriminierung (EU-Richtlinie 2007/78) doch angerechnet werden muss.
Sie sehen, man kann nicht einmal den Buchstaben des Gesetzes trauen. Dieses ist das Risiko bei der Verexcelung von Paragrafen Rechtskenntnisse und Excelkenntnisse müssen vorhanden sein...
Leider ist der § 622 BGB nicht ausreichend, um eine valide Berechnung durchführen zu können. Hinzu kommen die §§ 187 I und 188 I BGB, die sich mit Ereignisfristen beschäftigen. Eine Kündigung ist leider eine solche. Dieses muss bei der Berechnung berücksichtigt werden. Also wird die Kündigung per se erst einen Tag nach erhalt wirksam, aber bei einer Frist von Wochen, Monaten oder Jahren wieder nach § 188 wieder ein Tag abgezogen - mathematisch gesehen mit +1 -1 gesehen keine Hürde, aber eben nicht wirklich richtig, wenn man diesen Umstand nicht berücksichtigt
Bis hierher konnten die Juristen folgen und der Excelamateur staunt, aber ab jetzt werden wir das Gleichgewicht wiederherstellen
Als Eingabezelle wurden A6 und C6 gewählt. Der Endnutzer gibt hier Beschäftigungsbeginn (A6) und den Tag des erhalts der Kündigung (C6) an. Beides in der Form: tt.mm.jjjj
Die erste Berechnung zum warmwerden ist easy - Fristbeginn nach § 187 I (einen Tag nach Erhalt der Kündigung), also ist die Formel für A10: =C6+1.
In Absatz I des § 622 BGB verbürgt sich der Grundsatz, dass bei bis zu Zwei Jahren eine Frist von vier Wochen zum 15 oder eines Ende des Monats gekündigt werden kann.
Das teilte der Sternenhimmelstuermer in zwei Schritte jeweils für die Mitte des Monats oder das Ende des Monats auf:
1. Schritt: E14=$A$10-1+28 Die Dollarzeichen wurden verwendet (Taste F4), damit die Formel nach E15 kopiert werden kann. Vier Wochen entsprechen 28 Tagen,-1, da bei vier Wochen wieder nach § 188 I BGB ein Tag abgezogen wird.
2. Schritt: Jetzt wird es schwierig, da im ersten Fall der folgende 15. - also Mitte des Monats der früheste Kündigungstermin wäre - oder als Formel :
F14=WENN(TAG(E14)<16;DATUM(JAHR(E14);MONAT(E14)+1;1)+14;DATUM(JAHR(E14);MONAT(E14)+2;1)+14)
Das ist auf dem zweiten Blick unkompliziert. Es handelt sich um eine einfache wenn-Formel. Bis zum ersten Semikolon findet die erste Prüfung statt: Ist der Tag in der Zelle E14 kleiner als 16, dann berechne bis zum zweiten Semikolon das Datum (ist eine Verschachtelung der Funktion Datum, welches sich aus dem Jahr aus Zelle E14; dem nächsten Monat E14+1(Monat); und dem ersten Tag dieses Monats zusammensetzt). Addiere das Ergebnis mit 14 Tagen.
Hier halten wir kurz inne. Die +14 ergibt 15 Tage. Warum die Formel so gestaltet wurde wird noch erklärt (einfach 15 eintragen hätte doch auch funktioniert und die + 14 erspart).
Der Rest der Wennformel ist die Sonstspalte von der Funktion. Jetzt bleiben nur noch die Prüftage übrig, die über den 15. liegen. Die werden also durch Ausschlussverfahren ermittelt - was nicht kleiner >16, ist also größer 15 und wird nun als Rechengrundlage verwendet.
Diese Tage müssen nun zwei Monate vom Fristbeginndatum weiter liegen, also dieselbe Formel mit einer kleinen Variation: Der Monat aus Zelle E14 wird mit 2 addiert (Monat(E15)+2).
Wir brauchen also keine Rundungsfunktion. Es geht doch nichts über einen kleinen Dreisatz wider der Pisastudie.
Dann noch die Alternative für den Ende des Monats.
1. Schritt: Der kann unterschiedlich sein (28, 29, 30, 31), was aber nicht wirklich ein Problem in Excel ist:
erster Schritt bleibt wie oben (E15=$A$-1+28).
2. Schritt die erfrischend kurze Formel: F15: =DATUM(JAHR(E15);MONAT(E15)+2;1)-1
Der Ansatz ist hier anders. Es gibt nur einen Fall. Das Enddatum des nächsten Monats ist in jedem Fall die höchste oder gleiche Zahl. Also reicht hier die Datumsformel ohne Wennformel. Wir addieren also zwei Monate zum Ursprungsdatum (Monat(E15)+2). Dann landen wir durch die Tagesangabe 1 auf den ersten Tag des zweiten Monats. Danach ziehen wir einen Tag -1 ab und landen so bei einem Monat und dem letzten Tag des ersten Monats (28,29,30, oder 31, je nach Monat. Excel berechnet präzise die Datumsangaben).
Erinnern Sie sich ans erste Innehalten bei der Berechnung für die Mitte des Monats? Da addierte der Sternenhimmelstuermer 14 Tage zum Ersten.
Die Formel Datum benutzt der Sternenhimmelstuermer in zwei Varianten: Einmal mit Addition und einmal mit Subtraktion. Das ist sinnvoll, da Sie bei der Subtraktion 1-1 oder einer höheren Zahl innerhalb der Klammer eine 0 oder negative Zahl herausbekommen würden, was zu einer Fehlermeldung führt. Deshalb empfiehlt der Sternenhimmelstuermer diese allgemeingültige Variante.
Damit ist der Probezeitraum ausgerechnet.
Das arbeiten in Paragrafen ermöglicht in vielen Fällen ein Gliederungsschema, so dass Sie die Absätze auch als Gliederungsschema für Excel benutzen können.
Erstmal müssen Sie die Beschäftigungsjahre (dazu zählt nicht die Ausbildung) errechnen:
F25 =DATEDIF(A6;C6;"Y")
Hm, Datedif wird noch unterstützt, ist ziemlich ungebräuchlich, abern diesem Fall die einfachste Formel zur Anzeige eines Jahres. Es wird die Differenz aus A10 (Datum des Beschäftigungsbeginn) - A6 (Datum des Fristbeginns) gebildet und als Y Jahre angezeigt. Ist manchmal ein Schweizer Messer.
Als nächstes Berechnen Sie die Kündigungsfristen nach Nr. 1 - 8 des o. a. Paragrafen. Der Übersicht halber für jede Nummer eine Formel.
Die Syntax von Datedif sollten Sie sich mal anschauen.
Der Sternenhimmelstuermer gibt für die Nummer 1 und 2 des o. a. Absatzes eine Beschreibung. Den Rest können Sie selbst anpassen - eine Klasse Übung, ob Sie fit sind:
D26 =WENN(UND(F25>1;F25<5);DATUM(JAHR(A10);MONAT(A10)+2;1)-1;"nicht zutreffend")
Sie sehen, die Wennformel ist wieder Dreh- und Angelpunkt. Wenn Sie die verstanden haben, dann steht der Schlüssel für fast sämtliche Türen offen:
=WENN(UND(F25>1;F25<5);
Bis hierher wieder erstmal die Bedingung der Wennformel. Der Sternenhimmelstuermer verschachtetelte das mit einer und() - Funktion, da die
Prüfung einen Zeitrahmen enthält. Formel als Text: Wenn die Zahl der Beschäftigungsjahre zwischen 2 und 4 liegt, dann...oder eben mathematisch die Zahl größer als >1 und kleiner als >5 ist; dann...
Hier halten wir wieder Inne. Eine Formel bedingt immer Klammern (). Gerne wird mal eine vergessen. Besonders die Schlusskammer der Wenn-Funktion wird gerne vergessen - die kommt ganz am Schluss...
DATUM(JAHR(A10);MONAT(A10)+2;1)-1
Nach dem Semikolon der Dann - Teil der Wennfunktion oder was soll Excel machen? Das sieht wieder nur so schlimm aus, weil wir uns die einzelnen Datumsbestandsteile vom Fristbeginn zusammensuchen und beim Monat zwei Monate addieren, den ersten Tag festlegen und das Ganze -1 also einen Tag nehmen. Diese Vorgehensweise ist wieder durch den Fristbeginn bedingt, wie es im vorherigen Absatz beschrieben wurde.
"nicht zutreffend")
Das ist die Alternative - oder sonst der Wennfunktion. Die schließende Klammer beendet die Funktion. Der Text nicht zutreffend steht in Anführungszeichen. Das ist bei Text immer so. Angezeigt wird der Text, wenn die Beschäftigungszeit außerhalb des definierten Zeitraums liegt.
Die zweite Formel im Schnelldurchlauf, da sich nicht wirklich etwas ändert:
D27 =WENN(UND(F25>4;F25<8);DATUM(JAHR(A10);MONAT(A10)+3;1)-1;"nicht zutreffend")
Im ersten Teil wieder die Prüfung mit veränderten Zeiträumen; im zweiten Teil die Folge, wenn der Beschäftigungszeitraum im Prüfzeitraum liegt. Dann wird hier das Datum angezeigt. Ansonsten wird wieder nicht zutrefend angezeigt. Irgendeine der Formeln greift immer.
Kopieren der Formel ist nur beschränkt möglich - man könnte die F25 natürlich absolut setzen.
Die letzte Formel für diesen Paragrafen ist eher Easy - nur das es sich um eine Beginnfrist nach § 187 II handelt. C37 ist also C6 - das Kündigungsdatum maßgeblich.
Im C38 steht dementsprechend C37 +14-1
Am letzten Tag um 24.00 Uhr endet die Frist, also -1.
Nun kann zusätzlich dieser Tag noch auf einem Sa, So oder anderen Feiertag fallen, was nach § 193 BGB mathematisch eine Fristverlängerung bewirkt. Für Sa und So nicht wirklich ein Problem
Mit einer einfachen wenn-Funktion können Sie das darstellen und folgend Formel in Zelle 39 anschauen:
=WENN(WOCHENTAG(C38)=7;C38+2;"kein Samstag")
Die Funktion Wenn wurde bereits erklärt. Bis zum ersten Semikolon ist die Prüfung, ob ein Samstag in C38 vorliegt. 7 steht dabei für Samstag in der Wochentagformel. Eine Auflistung der Tage für die Wochenformel finden Sie hier. Im zweiten Dann-Teil. Trifft die Aussage zu, wird das Datum mit zwei Tagen addiert. Ansonsten kommt im letzten Teil die lapidare Auskunft kein Samstag.
Dasselbe gilt für die Formel für Sonntag in Zelle C40:
=WENN(WOCHENTAG(C38)=1;C38+1;"kein Sonntag")
1 steht hier für Sonntag und Plus 1 ist dann der nächste Montag. O. K., Feiertage sind nicht berücksichtigt - das wäre über eine Sverweis-Formel realisierbar. Sämtliche Feiertage des Jahres in einer Matrix kombiniert mit einer Wenn-Formel. Im Kalender des ternenhimmelstuermers wurde das teilweise realisiert...
Anhand dieser kleinen Tabelle sind einige wichtige Formeln aufgeführt. Diese Tabelle wird noch ergänzt. Je nachdem, was der Sternenhimmelstuermer noch so findet. Die Beziehungen finden Sie bei der ersten Demotabelle:
Formel kurze Beschreibung
=WENN(TAG(E14)<16;DATUM(JAHR(E14);MONAT(E14)+1;1)+14;DATUM(JAHR(E14);MONAT(E14)+2;1)+14) Formel zur Berechnung der Mitte des Monats
=DATUM(JAHR(E15);MONAT(E15)+2;1)-1
Formel zur Berechnung des Endes des Monats
=DATEDIF(A6;C6;"Y") Formel zurBerechnung von Jahren zwischen zwei Daten und Ausgabe in der Form Jahre - Tage: d; Monate: n ebenfalls möglich
=WENN(UND(F25>1;F25<5);DATUM(JAHR(A10);MONAT(A10)+2;1)-1;"nicht zutreffend") Formel zur Berechnung von einem definierten Zeitraum in einer Wennfunktion mit zwei Alternativen - Datumsausgabe oder Anzeige, dass der Wert nicht in diesem Zeitraum liegt.
=WENN(WOCHENTAG(C38)=1;C38+1;"kein Sonntag") Formel zur Überprüfung, ob ein Wochenendtag vorliegt. Wenn ja, wird der folgende Montag angezeigt.
=DATUM(JAHR(A1);RUNDEN(MONAT(A1)/3+0,49999;0)*3+1;1)-1 letzter Tag vor einem Quartalsende
=DATUM(JAHR(A1);OBERGRENZE(MONAT(A1);3)+1;1)-1 letzter Tag vor einem Quartalsende
=DATUM(JAHR(A1);OBERGRENZE(MONAT(A1);3)+1;1) erster Tag nach dem Quartal Anzeige einer Quatarlssfrist in Excel. Diese Formel berechnet eine Quartalsfrist: Quelle
Der Sternenhimmelstuermer verbesserte die Formel ein wenig, so dass Sie für jeden nachvollziehbar und kürzer wird.
Statt die eher schwerfällige zitierte Konstruktion wird die Funktion Obergrenze für den Monat verschachtelt. Das Intervall (vor dem Semikolon) ist eine 3, also 3,6,9 und 12 Monat. Der wird +1 Monat genommen und danach -1 außerhalb der Klammer Das gesamte Datum der Funktion - 1 Tag genommen, um den letzten Tag des Monats herauszubekommen.
Ist zur Zeit die beste Formel zum Thema: Quartalsende als Datum Anzeigen lassen. Und eine Eigenentwicklung. Der Sternenhimmelstuermer ist megastolz auf sich.
Daraus können Sie im Prinzip auch sämtliche anderen Tagesfristen, Monatsfristen oder Jahresfristen ableiten. Z. B. können Sie den Ende jedes zweiten Monats bestimmen, in dem Sie 2 als Intervall nehmen. Wollen Sie den ersten Tag eines neuen Quartals, dann einfach die -1 weglassen.
Fazit: Die Obergrenzenformel ist wesentlich flexibler und kann für Tage Monate und Jahre ebenfalls flexibel eingesetzt werden.
1. Paragrafen sind als Exceltabelle darstellbar und können für Profis eine Hilfe sein.
2. Wegen der vielen Widrigkeiten, Gesetzesänderungen, Gerichtsurteile ist das Erstellen von validen Tabellen schwer, Aktualisierungen sind Pflicht, also eher ungeignet für Hobbyuser. Gerade deshalb wurde der § 622 BGB ausgewählt. An der Diskreminierung der unter 25 jahren alten User sieht man deutlich - man kann sich nicht einmal auf den Gesetzestext verlassen.
3. Letzten Endes kann Ihnen nur ein Rechtsexperte - sprich Anwalt wirklich weiterhefen.
4. Nicht alle Normen sollten Sie klaglos akzeptieren. Die eher arbeitgeberfreundliche Urfassung des Paragrafen war mit Verlaub gesagt - eine Sauerei! Oder im Juristendeutsch eine Diskriminierung. Die EU-Gesetzgebung (Richtlinien) wird noch in einigen Bereichen Verbesserungen bringen...
5. Hingegen ist für Studenten eine Verexcelung von Paragrafen sinnvoll. Danach haben Sie den Paragrafen begriffen. Ruhig also dem Professor/Lehrer zeigen.
6. Falls irgendein Betroffenen diese Tabelle weiterhilft, auch der Sternenhimmelstuermer freut sich über ein Dankeschön...in Form von Worten - ist ein ganz schöner Zeitaufwand...

References: § 622
 § 622
 § 622
 § 188
 § 187
 § 622
 § 188
 § 187
 § 193
 § 622