282 Aufrufe
Gefragt in Tabellenkalkulation von ahorn38 Experte (2.7k Punkte)
Hallo,

ich habe eine Zeile mit einer Anzahl von Einträgen im Format "dd:mm:yyyy" und möchte das erste (und später das letzte) Datum im Monat "Okt" finden.
Hat jemand eine Idee? Danke für jeden Tipp und Gruß A.

5 Antworten

0 Punkte
Beantwortet von ahorn38 Experte (2.7k Punkte)
Hallo,

mir ist dazu nur eine Schleife eingefallen:
[code]For j = 4 To Cells(3, 256).End(xlToLeft).Column
    If Format(DateSerial(1, Month(Cells(3, j)), 1), "MMM") = strMonat Then   ' erstes Datum im Monat
        iColB = j
        Exit For
    End If
Next[/code]
Gibt's was besseres?
Gruß A.
0 Punkte
Beantwortet von
Hallo Andres ^^

Eigentlich müßte das mit formel kkleinste/kgrösste/wenn leicht zu realisieren sein!

Wir hoffen daher erstmal auf unsere Formelfreaks :-)

Die entstandene Formel kannst du dann mit Application.Worksheetfunction ... oder auch mit Makrorecorder die Formel eingeben um entsprechenden Code in Vba zu nutzen!

Ansonsten schonmal ein guter Ansatz schleifen zu reduzieren bzw zu ersetzen!

Gruß Nighty
0 Punkte
Beantwortet von ahorn38 Experte (2.7k Punkte)
Hallo,
ok danke für den Hinweis. Mal sehen ob noch jemand einen Tipp hat. Ich wollte eben gerade die Schleife vermeiden auch wenns erst mal funktioniert.
Gruß A.
0 Punkte
Beantwortet von
Hallo Andreas,

das kommt ganz darauf an, was du vorhast. Willst du nur wissen ob ein Monat 30 oder 31 Tage hat? Dann reicht die Formel =MONATSENDE(DATUM(2017;10;1);0). Statt Datum() kannst du dich auch auf eine beliebige Zelle mit Datumseintrag beziehen.

Oder willst das Ergebnis weiterverarbeiten? Dann könnte man für den letzten Eintrag eines Monats, mit WVERWEIS rangehen. Funktioniert wie SVERWEIS nur quer rüber. Das hat dann den Vorteil, dass du einen zugehörigen Wert zurückgeben kannst, der in der Spalte weiter unten steht.

z.B. =WVERWEIS(DATUM(2017;10;31);3:3;1)
oder im VBA: MsgBox CDate(Application.HLookup(CLng(DateSerial(2017, 10, 31)), Range("3:3"), 1))

Durch Weglassen des vierten Parameters wird der letzte Wert zurückgegeben der kleiner oder gleich dem Suchwert ist. z.B. 29.10.17. Dazu sollten die Werte möglichst aufsteigend sortiert sein, andernfalls wird nicht der höchste, sondern der am weitesten rechts liegende Oktoberwert zurückgegeben, auch wenn dieser irgendwo mitten im Oktober liegt.
Für den Ersten Oktobereintrag (z.B. 3.10.) würde die Formel jedoch den letzten Septemberwert zurückgeben. Hier könnte man auf Index und Vergleich ausweichen und einfach eine Zelle weiter rechts vom Ergebnis zurückgeben.

=INDEX(3:3;1;VERGLEICH(DATUM(2017;10;1);3:3;1)+1)
oder über VBA: MsgBox CDate(Application.Index(Range("3:3"), 1, _
Application.Match(CLng(DateSerial(2017, 10, 1)), Range("3:3"), 1) + 1))

Auch hier sollten die Daten aufsteigend sortiert sein. Außerdem sollte sichergestellt sein, dass sich überhaupt Oktoberwerte im Blatt befinden. Sonst wird durch das Verschieben vom 30. Sept um 1 nach rechts der nächsthöhere Wert (z.B. 1.November) zurückgegeben. Das kannst du bei Bedarf mit Wenn-Funktion ausschließen.

Wie gesagt, kannst du statt dem Wert von Zeile 1 im dritten Parameter von Wverweis, bzw. im zweiten Parameter von Index auch jeden andere beliebige Zeile innerhalb der Spalte zurückgeben.

Gruß Mr. K.
0 Punkte
Beantwortet von
Hallo Mr. K.  ^^

Klasse Lösung!

Gruß Nighty
...