Office: (Office 2007) Kündigungsfristen berechnen

Sebastian Schulz · 28. Juli 2010

Hallo zusammen,

ich habe in einer Datenbank verschiedene Kündigungsfristen.

3 Arbeitstage zum Ende einer Woche,
5 Arbeitstage zum Ende einer Woche,
14 Tage zum Ende eines Monats,
20 Tage zum Ende eines Monats,
6 Monate zum Ende des Jahres,
...

Nun soll er mir in einem Textfeld anzeigen, wann der nächste frühestmögliche Kündigungsmöglichkeit ist.

Kann mich hier jemand unterstützen?

Vielen Dank und ich wünsch euch ein schönes Wochenende.

:)

maikek · 30. Juli 2010

Die Funktion DateDiff() ist in dem Fall dein Freund.
maike

Lanz Rudolf · 30. Juli 2010

Hallo
evtl. brauchst du noch folgendes:

Code:

Erster Tag der Woche
VBA Format(Jetzt()-Wochentag(Jetzt();0)+1;"tt\.mm\.jjjj")
SQL Format(Now()-Weekday(Now(),0)+1,"dd\.mm\.yyyy")
Letzter Tag der Woche
VBA Format(Jetzt()-Wochentag(Jetzt();0)+7;"tt\.mm\.jjjj")
SQL Format(Now()-Weekday(Now(),0)+7,"dd\.mm\.yyyy")
Erster Tag im Vor Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt())-1;1)
SQL DateSerial(Year(Now()), Month(Now()) - 1, 1)
Letzter Tag im Vor Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt());0)
SQL DateSerial(Year(Now()), Month(Now()), 0)
Erster Tag im Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt());1) oder
DateValue(Format(Datum(),"mm/jjjj")) gibt 01.mm,jjjj
SQL DateSerial(Year(Now()), Month(Now()), 1) oder
DateValue(Format(Date(),"mm/yyyy"))

Letzter Tag im Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt())+1;1) -1
SQL DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1
Tage bis Monatsende
VBA DatDiff("t"; Jetzt(); DatSeriell(Jahr(Jetzt()); _
Monat(Jetzt()) + 1; 1) - 1) DateDiff("d", Now(), DateSerial(Year(Now()), _
SQL Month(Now()) + 1, 1) - 1)
Erster Tag im Nächst.Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt())+1;1)
SQL DateSerial(Year(Now()), Month(Now()) + 1, 1)
Letzter Tag im Nächst. Monat
VBA DatSeriell(Jahr(Jetzt());Monat(Jetzt())+2;0)
SQL DateSerial(Year(Now()), Month(Now()) + 2, 0)
Erster Tag im Quartal
VBA DateSerial(Jahr(Date); _
(DatePart("q"; Datum) - 1) * 3 + 1; 1)
SQL DateSerial(Year(Date), _
(DatePart("q", Date) - 1) * 3 + 1, 1)
Letzter Tag im Quartal
VBA DateSerial(Jahr(Datum); _
DatePart("q"; Datum) * 3 + 1; 0)
SQL DateSerial(Year(Date), _
DatePart("q", Date) * 3 + 1, 0)
ErsterTag im Jahr
VBA DatSeriell(Format(Jetzt();"jjjj");1;1)
SQL DateSerial(Format$(Now(), "yyyy"), 1, 1)
LetzterTag im Jahr
VBA DatSeriell(Format(Jetzt();"jjjj");12;31)
SQL DateSerial(Format$(Now(), "yyyy"), 12,31)
Tage bis Jahresende
VBA DatDiff("t"; Jetzt(); _
DatSeriell(Jahr(Jetzt()); 12; 31) - 1)
SQL DateDiff("d", Now(), _
DateSerial(Year(Now()), 12, 31) - 1)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

gpswanderer · 30. Juli 2010

Hallo,
und nicht zu vergessen, eine Feiertagstabelle.

achtelpetit · 31. Juli 2010

Es könnte eine gute Idee sein, überhaupt eine Kalendertabelle als Grundlage zu nehmen. Dies Tabelle würde alle Eigenschaften eines Datums aufnehmen.
Es ist etwas aufwendig, die Tabelle zu erstellen, in vielen Fällen aber sicher weniger aufwendig als alle möglichen Spezialfälle zu berechnen:

Feiertage abhängig vom Standort
welcher Tag gilt als Wochenbeginn
bewegliche Feiertage
der bekannte Kalenderwochen-Bug
juristisch definierte Zeiträume abweichend von der kaufmännischen Definition

Es gibt sicher noch mehr.

ebs17 · 31. Juli 2010

Hallo Sebastian,

vielleicht dies als erster Ansatz (und Diskussionsgrundlage):
Code:

' in ein Standardmodul
Public Enum enumFrist
[3 Arbeitstage zum Ende einer Woche] = 1
[5 Arbeitstage zum Ende einer Woche] = 2
[14 Tage zum Ende eines Monats] = 3
[20 Tage zum Ende eines Monats] = 4
[6 Monate zum Ende des Jahres] = 5
End Enum

Public Function fTermination(ByVal Stichtag As Variant, ByRef Frist As enumFrist) As Variant
Dim dRes As Date
Dim dComp As Date
If IsNull(Stichtag) Or Not IsDate(Stichtag) Then
fTermination = Null
Exit Function
End If
Select Case Frist
Case 1
Case 2
Case 3
dComp = DateSerial(Year(Stichtag), Month(Stichtag) + 1, 0)
If DateDiff("d", Stichtag, dComp) >= 14 Then
dRes = dComp
Else
dRes = DateAdd("m", 1, dComp)
End If
Case 4
dComp = DateSerial(Year(Stichtag), Month(Stichtag) + 1, 0)
If DateDiff("d", Stichtag, dComp) >= 20 Then
dRes = dComp
Else
dRes = DateAdd("m", 1, dComp)
End If
Case 5
dComp = DateSerial(Year(Stichtag), 13, 0)
If DateDiff("m", Stichtag, dComp) >= 6 Then
dRes = dComp
Else
dRes = DateAdd("yyyy", 1, dComp)
End If
End Select
fTermination = dRes
End Function
' ---------------------------------------------------------

' Beispielaufruf
Me!txtFeld = fTermination(#4/4/2010#, [6 Monate zum Ende des Jahres])
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Für die Fälle 1 und 2 (Arbeitstageberechnungen) müsste noch wie bereits angesprochen eine Kalender-/Feiertagstabelle eingebunden werden, außerdem, wenn es halbwegs variabel eingesetzt werden soll, eine regionale Abhängigkeit der Feiertage. Das würde ich später noch ergänzen, dauert aber etwas.

Thomas Möller · 31. Juli 2010

Hallo Eberhard,

ich persönlich baue die Enumeration anders auf. Dabei geht es mir um die Werte in der Enumeration. Hier lasse ich auf keinen Fall Leerzeichen in den Bezeichner zu. Das erspart mir die leseunfreundliche Verwendung der eckigen Klammern. Statt dessen schreibe ich im CamelCase:

Code:

Public Enum eFrist
DreiArbeitstageZumEndeDerWoche = 1
FuenfArbeitstageZumEndeDerWoche = 2
VierzehnTageZumEndeDesMonats = 3
ZwanzigTageZumEndeDesMonats = 4
SechsMonateZumEndeDesJahres = 5
End Enum
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Im Select-Case-Statement verwende ich dann statt der Werte (1,2,3 etc.) die definierten Konstanten aus der Enumeration:

Code:

Select Case Frist
Case eFrist.DreiArbeitstageZumEndeDerWoche
' ...
Case eFrist.FuenfArbeitstageZumEndeDerWoche
' ...
Case eFrist.VierzehnTageZumEndeDesMonats
dComp = DateSerial(Year(Stichtag), Month(Stichtag) + 1, 0)
If DateDiff("d", Stichtag, dComp) >= 14 Then
dRes = dComp
Else
dRes = DateAdd("m", 1, dComp)
End If
Case eFrist.ZwanzigTageZumEndeDesMonats
dComp = DateSerial(Year(Stichtag), Month(Stichtag) + 1, 0)
If DateDiff("d", Stichtag, dComp) >= 20 Then
dRes = dComp
Else
dRes = DateAdd("m", 1, dComp)
End If
Case eFrist.SechsMonateZumEndeDesJahres
dComp = DateSerial(Year(Stichtag), 13, 0)
If DateDiff("m", Stichtag, dComp) >= 6 Then
dRes = dComp
Else
dRes = DateAdd("yyyy", 1, dComp)
End If
End Select
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ich meinen Augen wird der Code damit aussagekräftiger. Ich muss nicht mehr überlegen, was die 1 oder die 2 oder die 3 bedeutet. Statt dessen kann ich aus dem Namen des Wertes der Enumeration direkt dessen Bedeutung ablesen.
Dadurch das ich zusätzlich den Namen der Enumeration angeben vermeide ich eventuelle Überschneidungen mit anderen definierten Konstanten.

CU

Josef P. · 31. Juli 2010

... ich würde die Kündigungsfristen mit den notwendigen Parametern in eine Tabelle stecken, damit sie bei Bedarf erweitert bzw. angepasst werden können. *wink.gif*

mfg
Josef

ebs17 · 31. Juli 2010

\@Thomas: Dein Hinweis ist sicher sehr berechtigt (und wird bei mir abgespeichert). Der Hinweis auf Diskussionsgrundlage ist Programm. Da werfe ich gerne einen Stein in Wasser, um mir die Wellen zu betrachten.

Das war meine erste Auseinandersetzung mit einer Enumeration - zumal mit eher zu vermeidenden Ausdrücken (Ziffer am Anfang, Leerzeichen) - um ich an dieser Stelle noch nicht mit Tabellen zu beschäftigen. Und ich wollte die Ausdrücke gut leserlich halten.

In der weiteren Verwendung in der Datenbank übergibt man das Argument Frist besser als ID aus einer Nachschlagetabelle (dargestellt z.B. in einem Kombifeld) als Longwert. Der Anwender hat schließlich nichts mit dem Schreiben der Funktion, sondern bestenfalls mit der Auswahl bereitgestellter Argumente zu tun. Da bin ich schon längst bei Josef´s Vorschlag, wollte aber die Einbeziehung von Tabellen noch ein wenig aufschieben.

Rückfrage an Sebastian: Soll das eher eine Einzelberechnung werden, oder planst Du eher Massenentlassungen (> 1000) vor?

ebs17 · 31. Juli 2010

Hier ist ein kleines Beispielprojekt in starker Anlehnung an den OBD Kalender von Klaus Oberdalhoff. Durch die Übernahme dort enthaltener Tabellen und Routinen könnte auch der Kalender selber übernommen werden.
Die Berechnung der Kündigungstermine ist etwas abgewandelt:
Code:

Private Function fTermination(ByVal Stichtag As Variant, ByVal FristID As Long, _
ByVal DiffTage As Long, Optional ByVal Region As String = "B13") As Variant
Dim dRes As Date
Dim dComp As Date
Dim sSQL As String
If IsNull(Stichtag) Or Not IsDate(Stichtag) Then
fTermination = Null
Exit Function
End If
Select Case FristID
Case 1, 2
sSQL = "SELECT Min(X.HDatum) AS Termin FROM (SELECT Q.HDatum FROM" & _
" (SELECT W.HDatum, W.Wochentagnr FROM (SELECT K.HDatum, K.Wochentagnr FROM" & _
" tblKalender K WHERE K.Wochentagnr < 6) W LEFT JOIN" & _
" (SELECT A.Feiertagsdat FROM tblFeierStd FS INNER JOIN tblAlleFeiertage A" & _
" ON FS.Feiertagsname = A.Feiertagsname WHERE FS.XXXX = True) F" & _
" ON W.HDatum = F.Feiertagsdat WHERE F.Feiertagsdat Is Null) Q" & _
" WHERE Q.Wochentagnr = 5 AND Q.HDatum >= " & CLng(Stichtag) & " + " & _
DiffTage & ") X"
' Region einstellen
sSQL = Replace(sSQL, "XXXX", Region)
dRes = DLookupSQL(sSQL)
Case 3, 4
dComp = DateSerial(Year(Stichtag), Month(Stichtag) + 1, 0)
If DateDiff("d", Stichtag, dComp) >= DiffTage Then
dRes = dComp
Else
dRes = DateSerial(Year(dComp), Month(dComp) + 2, 0)
End If
Case 5
dComp = DateSerial(Year(Stichtag), 13, 0)
If DateDiff("m", Stichtag, dComp) >= 6 Then
dRes = dComp
Else
dRes = DateAdd("yyyy", 1, dComp)
End If
End Select
fTermination = dRes
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Sebastian Schulz · 9. August 2010

Hallo Zusammen,

ich bin grade aus dem Urlaub zurück gekommen und es hat mich fast rückwärts vom Stuhl gehauen. Das ist ja richtig genial was Ihr hier gemacht habts. Mit einer solchen Qualität und ausgereiften LÖsung habe ich nicht gerechnet. Vielen Dank.

Das wäre doch schon fast etwas fürs Code-Archiv.

Office: (Office 2007) Kündigungsfristen berechnen

Kündigungsfristen berechnen

Kündigungsfristen berechnen

Kündigungsfristen berechnen

Kündigungsfristen berechnen

Kündigungsfristen berechnen - Similar Threads - Kündigungsfristen berechnen

Positive und Negative Stunden berechnen

Uhrzeiten berechnen

Umsatzsteuer mit Dropdown berechnen - Rechnung

Provisions-Berechnung mit Pivot-Tabelle

Alter aus Datum oder nur Jahr berechnen

Berechnen von Kündigungsfristen

Formel für Kündigungsfristen