[Excel] Monatsübergreifenden Zeitraum nach Monaten aufschlüsseln

[Fraver]

Hallo,



für einen Freund bastele ich immer noch an einer Tabelle, die ihm seine Arbeitstage auf Montage berechnet.

In einem anderen Thema hatte ich das Problem mit den bundeslandbezogenen Feiertagen, welches ich aber gelöst habe.



Der Aktuelle Stand der Tabelle:



Bisher gibt er den Anreisetag und den Tag der Heimreise an. Daraus und anhand der Feiertage wird berechnet, wie viel Tage er in diesem Zeitraum gearbeitet hat.

In den meisten Fällen ist der Anreisetag ein Sonntag. Fällt also sowieso raus. Sollte dem mal nicht so sein, wird ein Tag abgezogen.



Bis hier hin ist alles wunderbar.



Jetzt kommt das Problem:



Die Ausgabe soll immer für einen Monat sein. Jetzt kommt es aber vor, das die Anreise zum Beispiel am 23.03.2014 war und die Heimreise am 04.04.2014.



Jetzt wäre es schön, wenn es eine Formel gibt, die erkennt: Aha, die Heimreise war in einem neuen Monat und der Monat der Anreise hat (in dem Fall) 31 Tage.



Wenn dem so ist, soll als Heimreisedatum der 31.03. genommen werden. (Es geht wohlgemerkt nur um die Arbeitstage)



Im nächsten Monat, im April, dann genau anders rum.

Anreise am 23.03.2014 und Heimreise am 04.04.2014. Dieses Mal soll dann aber die Anreise am 01.04. sein und darf nicht, wie oben beschrieben, abgezogen werden.





Es wäre schön, wenn ihr mir da weiterhelfen könnt. Und das in Form von Formeln. Bitte keine Makros.



Vielen Dank schon Mal im Voraus,



Andy

 

[Yaslaw]

Keine fertige Lösung, aber ein Ansatz wie du die Differenz pro Monat ermitteln kannst

Option Explicit


Public Sub test1()
    Dim anreise     As Date:    anreise = #3/23/2014#
    Dim rueckreise  As Date:    rueckreise = #4/4/2014#
    'Periode-Start der ersten Periode
    Dim first As Date:          first = anreise
    'Periodenende der ersten Periode
    'Monatsende oder Rückreise. Je nachdem was kleiner ist
    Dim last As Date:           last = least(rueckreise, lastOfMonth(first))
    Dim diffDays    As Long
    
    Do
        '-----------------------
        'Der folgende Abschnitt berechnet die Differenz
        '//TODO: Tagesdifferenz mit Berücksichtigung der Feirtage
        
        'Beispieldifferenz mit normaler Rechnung
        diffDays = DateDiff("d", first, last)
        Debug.Print first & " - " & last & ": " & diffDays
        
        'Ende der Differnezberechnung
        '-----------------------
        
        'Nächste Periode ermitteln
        first = DateSerial(Year(first), Month(first) + 1, 1)
        last = least(rueckreise, lastOfMonth(first))
    Loop While rueckreise >= first
    

End Sub

Private Function lastOfMonth(ByVal iDate As Date) As Date
    'Monatsende ermitteln: Monat(anreise) + 1Monat - 1Tag
    lastOfMonth = DateSerial(Year(iDate), Month(iDate) + 1, -1)
End Function

'/**
' * Copyright mpl by ERB software | http://wiki.yaslaw.info
' * http://wiki.yaslaw.info/dokuwiki/doku.php/vba/functions/index#least
' * Gibt den Kleinsten aus einer unbestimmten Menge von Werten zurück
' * @param  Keine Objekte
' * @return Grösster Wert
' * @example least("Hallo Welt", 42, "Mister-X") -> 42
'*/
Private Function least(ParamArray items() As Variant) As Variant
    least = items(LBound(items)): Dim item As Variant: For Each item In items
        If item < NZ(least) Then least = item
    Next item
End Function

'/**
' * Copyright mpl by ERB software | http://wiki.yaslaw.info
' * http://wiki.yaslaw.info/dokuwiki/doku.php/vba/functions/index#greatest
' * Gibt den Grössten aus einer unbestimmten Menge von Werten zurück
' * @param  Keine Objekte
' * @return Grösster Wert
' * @example greatest("Hallo Welt", 42, "Mister-X") -> Mister-X
'*/
Private Function greatest(ParamArray items() As Variant) As Variant
    Dim item As Variant: For Each item In items
        If NZ(item) > NZ(greatest) Then greatest = item
    Next item
End Function

'/**
' * Wandelt NULL in Empty oder einen Defaultwert
' * @param  Variant
' * @param  Variant
' * @return Variant
' */
Private Function NZ(ByRef iValue As Variant, Optional ByRef iDefault As Variant = Empty) As Variant
    If IsNull(iValue) Then
        NZ = iDefault
    Else
        NZ = iValue
    End If
End Function
n

Die Ausgabe im Direktfenster

23.03.2014 - 30.03.2014: 7
01.04.2014 - 04.04.2014: 3

 

[Zvoni]

Diese Zeile kommt mir doch sehr seltsam vor:

lastOfMonth = DateSerial(Year(iDate), Month(iDate) + 1, -1)

Ich dachte immer der "nullte" eines Monats ist der letzte Tag des Vormonats, oder habe ich da was falsch in Erinnerung?
Ich hätte jetzt eher das hier erwartet:

lastOfMonth = DateSerial(Year(iDate), Month(iDate) + 1, 0)

 

[Yaslaw]

Hast Recht.
Ein kleiner Fehler meinerseits.

Warum müssen diese Funktionen bei Spezialfällen auch in jeder Sprache anders funktionieren....