VBA Datumsdifferenz, wieviele Tage in den Monaten.

Bild

Betrifft: VBA Datumsdifferenz, wieviele Tage in den Monaten.
von: Hermann
Geschrieben am: 08.11.2015 20:20:55

Hallo zusammen,
ich habe in einer Excelzelle z.B. A1 (Datum von) und in einer anderen Zelle
B1 (Datum bis) zwei Datumsangaben stehen. Nun möchte ich für ein komplettes Jahr 1000 Punkte vergeben. (1.1.20_ _ - 31.12.20_ _ ). Das Datum ist nicht Jahres-übergreifend.
Für einen kompletten Monat, der sich in der Datumsdifferenz befindet gibt es unterschiedlich viele Punkte. Siehe Bild.
Userbild
Mit Formeln habe ich das schon hinbekommen. Jetzt möchte ich dies in VBA so umsetzen,
daß mir die erreichten Punktezahl, wenn ich in den Zellen (A1) u.(B1) das Datum geschrieben habe, in C1 angezeigt werden.
Mein Ansatz war jetzt mit 12 Konstanten zu arbeiten. constjan=31, constmrz=31,usw.
Die Tage im Feb. wollte ich mit
TageimFeb = DateSerial(y, mFeb + 1, 1) - DateSerial(y, mFeb, 1)berechnen.
Beispiel: Die MonatspunktezahlJan(170) : constJan(31) x die errechneten Januartage aus der Datumsdifferenz.
Next MonatspunktezahlFeb(150):constFeb(28)x die errechneten Februartage aus der Datumsdifferenz.
Next
Die Summe ergibt dann meine Jahrespunktezahl.
Hierzu reichen meine VBA-Kenntnisse leider noch nicht.
Vielleicht kann mir von Euch jemand helfen.
Gruß, Hermann

Bild

Betrifft: AW: VBA Datumsdifferenz, wieviele Tage in den Monaten.
von: fcs
Geschrieben am: 08.11.2015 23:26:00
Hallo Herrmann,
es ist einfacher, die Konstanten in einem Array zu speichern mit den Werten pro Monat.
Dann kann man "einfach" in einer Schleife die Tageswerte zusammenzählen. Funktioin: fncPunkt
Ratsam ist es aber, die Vorgabewerte für die Monate im Tabellenblatt einzutragen und in der Funktion zu verarbeiten. Dann kann man Änderungen einfacher verarbeiten. Funktioin: fncPunkt2
Gruß
Franz
Tabelle1

 ABC
1StartEndePunkte
201.01.201410.07.2014587
3  587
4Monat NrMonat NamePunkte
51Januar170
62Februar150
73März130
84April80
95Mai40
106Juni40
117Juli 
128August 
139September30
1410Oktober80
1511November120
1612Dezember160

Formeln der Tabelle
ZelleFormel
C2=RUNDEN(fncPunkte(A2;B2); 0)
C3=RUNDEN(fncPunkte2(A2;B2;C5:C16); 0)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
'Benutzerdefinierte Funktion in einem allgemeinen Modul
Function fncPunkte(DatumStart As Date, DatumEnde As Date) As Double
    Dim arrPunkteTag(1 To 12)
    Dim arrPunkteMonat(1 To 12)
    Dim datDatum As Date
    Dim dblPunkte As Double
    
    arrPunkteMonat(1) = 170: arrPunkteTag(1) = arrPunkteMonat(1) / 31
    'Punkte pro Tag im Februar mit Berücksichtigung Schaltjahr
    arrPunkteMonat(2) = 150: arrPunkteTag(2) = arrPunkteMonat(2) / _
        (28 + IIf(Day(DateSerial(Year(DatumStart), 3, 0)) = 28, 0, 1))
    arrPunkteMonat(3) = 130: arrPunkteTag(3) = arrPunkteMonat(3) / 31
    arrPunkteMonat(4) = 80: arrPunkteTag(4) = arrPunkteMonat(4) / 30
    arrPunkteMonat(5) = 40: arrPunkteTag(5) = arrPunkteMonat(5) / 31
    'Summe der Punkte für Juni bis August = 40
    arrPunkteTag(6) = 40 / (30 + 31 + 31)
    arrPunkteTag(7) = arrPunkteTag(6)
    arrPunkteTag(8) = arrPunkteTag(6)
    arrPunkteMonat(9) = 30: arrPunkteTag(9) = arrPunkteMonat(9) / 30
    arrPunkteMonat(10) = 80: arrPunkteTag(10) = arrPunkteMonat(10) / 31
    arrPunkteMonat(11) = 120: arrPunkteTag(11) = arrPunkteMonat(11) / 30
    arrPunkteMonat(12) = 160: arrPunkteTag(12) = arrPunkteMonat(12) / 31
    
    For datDatum = DatumStart To DatumEnde
        dblPunkte = dblPunkte + arrPunkteTag(Month(datDatum))
    Next
    fncPunkte = dblPunkte
End Function
'Benutzerdefinierte Funktion in einem allgemeinen Modul
Function fncPunkte2(DatumStart As Date, DatumEnde As Date, arrPunkteMonat) As Double
    Dim arrPunkteTag(1 To 12)
    Dim datDatum As Date
    Dim dblPunkte As Double
    
    arrPunkteTag(1) = arrPunkteMonat(1, 1) / 31
    'Punkte pro Tag im Februar mit Berücksichtigung Schaltjahr
    arrPunkteTag(2) = arrPunkteMonat(2, 1) / _
        (28 + IIf(Day(DateSerial(Year(DatumStart), 3, 0)) = 28, 0, 1))
    arrPunkteTag(3) = arrPunkteMonat(3, 1) / 31
    arrPunkteTag(4) = arrPunkteMonat(4, 1) / 30
    arrPunkteTag(5) = arrPunkteMonat(5, 1) / 31
    'Summe der Punkte für Juni bis August sind in Zeile für Juni
    arrPunkteTag(6) = arrPunkteMonat(6, 1) / (30 + 31 + 31)
    arrPunkteTag(7) = arrPunkteTag(6)
    arrPunkteTag(8) = arrPunkteTag(6)
    arrPunkteTag(9) = arrPunkteMonat(9, 1) / 30
    arrPunkteTag(10) = arrPunkteMonat(10, 1) / 31
    arrPunkteTag(11) = arrPunkteMonat(11, 1) / 30
    arrPunkteTag(12) = arrPunkteMonat(12, 1) / 31
    
    For datDatum = DatumStart To DatumEnde
        dblPunkte = dblPunkte + arrPunkteTag(Month(datDatum))
    Next
    fncPunkte2 = dblPunkte
End Function


Bild

Betrifft: AW: VBA Datumsdifferenz, wieviele Tage in den Monaten.
von: Hermann
Geschrieben am: 09.11.2015 19:28:18
Hallo Franz,
vielen Dank für Deine Hilfe.
Es funktioniert perfekt.
Ich mußte mich noch mit der Handhabung und den Code ein wenig einlesen, weil ich mit einem Array in der Art noch nicht zu tun hatte.
Vielleicht findest Du mal ein bisschen Zeit und kannst mir noch ein paar Zeilen zum Code-Ablauf schreiben. Gibt es hierzu auch so eine Funktion wie den Einzelschrittmodus?
Nochmals vielen Dank Franz,
Das war perfekt.
Gruß, Hermann

Bild

Betrifft: AW: VBA Datumsdifferenz, wieviele Tage in den Monaten.
von: fcs
Geschrieben am: 09.11.2015 22:49:10
Hallo Hermann,
bei Functions musst du im VBA-Editor einne Haltepunkt in der 1. Code-Zeile der Function setzen. Dazu reicht ein Mausklick links neben der Zeile auf den Rahmen des Code-Fensters.
Wenn du das Tabellenblatt oder die Formel neu berechnest, dann stoppt das Makro hier und mit Taste F8 kannst du schrittweise weiter machen.
Sehr viel zu erläutern gibt es nicht.
In Variante 1 werden die Monatswerte den 12 Arraywerten zugewiesen, wobei für Juni bis August die Gesamtpunkte für Juni zugewiesen werden. In folgenden Anweisung (nach dem Doppeltpunkt oder in der nächsten Zeile werden dann die Punkte je Tag berechnet.
Die besonderheit ist hier der Februar, mit anderer Tageszahl in Schaltjahren.
mit (28 + IIf(Day(DateSerial(Year(DatumStart), 3, 0)) = 28, 0, 1))
wird die Anzahl Tage im Februar berechnet.
DateSerial(Year(DatumStart), 3, 0) liefert hierbei immer den letzten Tag im Februar ( 0. Tag vom März). Diesen kleinen Trick läßt die Funktion DateSerial zu.
In Variante 2 werden die Punkte der Monate als Parameter (Zellbereich) an die Function übergeben und einer Variablen vom Typ Variant zugewiesen. Das Entsprechende Array hat dann 12 Zeilen und 1 Spalte.
Der Rest ist dann in beiden Functions identisch.
Die Tage werden vom Starttag bis zum Endtag in der For-Next-Schleife (in 1er-Schritten) abgearbeitet und dabei die Tages-Punktzahlen entsprechend dem Monat des Tages aufsummiert.
Gruß
Franz

Bild

Betrifft: AW: VBA Datumsdifferenz, wieviele Tage in den Monaten.
von: Hermann
Geschrieben am: 10.11.2015 11:10:08
Hallo Franz,
vielen Dank für deine ausführliche Erklärung.
Zum Stichwort Haltepunkt setzen: Das wars nach dem ich gesucht hatte.
Die Matrix der Schreibweise muß ich mir in Bücher nochmal genau ansehen.
Weiterhin gutes Gelingen
Gruß, Hermann

 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA Datumsdifferenz, wieviele Tage in den Monaten."