Erster Samstag im Monat
Berthold Erfämper
ganz so einfach wie in der Überschrift ist es jedoch nicht. Zur
Berechnung des ersten Sa, So oder eines anderen Tages der Woche findet
man genügend im Netz. Mein Problem ist folgendes.
Ich benötige jeweils das Datum eines jeden Mi und Fr des Monats für ein
Jahr. Diese dann in zwölf Spalten in aufsteigender Reihenfolge. Nun kann
mal der Mi vom Datum her der erste Tag des Monats sein, mal der Fr. Und
welches Datum (Wochentag) ist der letzte des Monats?
Wie gesagt. Um den ersten x.ten zu berechnen, das wäre nicht das Problem.
Kennt jemand hierfür einen Ansatz zur Lösung?
Danke.
Berthold
Peter Schleif
>
> Ich benötige jeweils das Datum eines jeden Mi und Fr des Monats für ein
> Jahr. Diese dann in zwölf Spalten in aufsteigender Reihenfolge.
Monatsende wurde hier gerade erst ausführlich diskutiert.
http://groups.google.de/group/microsoft.public.de.excel/browse_frm/thread/8992fb74eb43cb35
Den ersten Mittwoch oder Freitag im Monat bekommst Du z.B. mit einer
VBA-Matrix-Funktion. Dazu 12 nebeneinander liegende Zellen markieren und
die folgende Funktion eintragen:
=ErsterMiOderFrImMonat(2010)
ACHTUNG: Matrix-Funktion!!!
Bitte Eingabe mit <SHIFT> <STRG> <RETURN> abschließen
Peter
Function ErsterMiOderFrImMonat(ByVal jahr As Integer)
Dim arr(0, 11) As Date
Dim erster As Date
Dim m As Integer
For m = 1 To 12
erster = DateSerial(jahr, m, 1)
arr(0, m - 1) = erster + IIf(erster Mod 7<5,4,6) - erster Mod 7
Next
ErsterMiOderFrImMonat = arr
End Function
Bernd P
Eine allgemeine Lösung findest Du hier:
http://sulprobil.com/html/weekday_in_month.html
Viele Grüße,
Bernd
Alexander Wolff
> arr(0, m - 1) = erster + IIf(erster Mod 7<5,4,6) - erster Mod 7
Das habe ich im folgenden als Formel verwendet :-). AktJ ist das
vierstellige Jahr.
A6:
=DATUM(AktJ;SPALTE(A1);1)-REST(DATUM(AktJ;SPALTE(A1);1);7)+WENN(REST(DATUM(AktJ;SPALTE(A1);1);7)<5;4;6)
Da das mittlere Glied eine "Konstante" bezogen auf das Datum ist, kann man
es noch wegkürzen:
A6:
=DATUM(AktJ;SPALTE(A1);1)+WAHL(REST(DATUM(AktJ;SPALTE(A1);1);7)+1;4;3;2;1;0;1;0)
oder gleich
A6:
=DATUM(AktJ;SPALTE(A1);WAHL(REST(DATUM(AktJ;SPALTE(A1);1);7)+1;5;4;3;2;1;2;1))
Die folgenden Datümer eines Monats werden dann über die nächsten 9 Zeilen
mit TT.MM;; oder TT;; formatiert und mit
A7: =(MONAT(A6+2+3*(REST(A6;7)=6))=MONAT(A$6))*(A6+2+3*(REST(A6;7)=6))
ausgefüllt (also bis L15).
Nun wäre es noch schön, wenn erste Mittwoche in der Zeile 6 begönnen und
erste Freitage in Zeile 7. Die letzte Zeile bliebe dabei - wie vorher - die
Zeile 15. Dann stimmen nämlich die Differenzen nebeneinanderstehender
Datümer in den Monaten zu den jeweiligen Vorgängern überein. Aber danach
war - noch - nicht gefragt.
Es gibt auch Ästheten, die statt zweier verschiedener Formeln gern für den
ganzen Block nur eine hätten. Diese würde jedoch recht lang. Außerdem gibt
es in jedem Monat einen zweiten {Mi|Fr, je nachdem, was eher kommt}, so dass
ein möglicher Grund für Uniformität, es gäbe ggflls überhaupt nur einen
Eintrag, wegfällt. Denn es gibt in jedem Monat 8-10 Einträge.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Berthold Erfämper
vielen Dank für die Formel / Funktion.
Leider war der Kopf schneller als die Finger.
Was ich brauche ist eine Übersicht aller Mi und Fr in einem Monat. Mein
Ansatz war den ersten Mi oder aber es kann ja auch ein Fr sein zu
errechnen und dann entsprechend das Datum plus zwei Tage bzw. plus fünf
Tage weiter aufrechnen zu lassen. Doch komme ich somit auf max. drei
Datumswerte im Monat und nicht auf alle.
Als Beispiel: Im Monat Apr 2010; 07.04.2010 Mi, 09.04.2010 Fr,
14.04.2010 Mi, 16.04.2010 Fr, 21.04.2010 Mi, 23.04.2010 Fr, 28.04.2010
Mi, 30.04.2010 Fr.
Und das dann für alle Monate eines Jahres.
Danke.
Berthold
Peter Schleif
>
> Was ich brauche ist eine Übersicht aller Mi und Fr in einem Monat.
>
Dann markiere bitte einen Bereich mit 10 Zeilen x 12 Spalten und benutze
diese VBA-Matrix-Funktion:
=AlleMiUndFrEinesMonats(2010)
ACHTUNG: Matrix-Funktion!!!
Bitte Eingabe mit <SHIFT> <STRG> <RETURN> abschließen
Peter
Function AlleMiUndFrEinesMonats(ByVal jahr As Integer)
Dim arr(1 To 10, 1 To 12) As String
Dim m As Integer
Dim d As Date
Dim i As Integer
For m = 1 To 12
i = 1
For d = DateSerial(jahr, m, 1) To DateSerial(jahr, m+1, 0)
If d Mod 7 = 4 Or d Mod 7 = 6 Then
arr(i, m) = CDate(d)
i = i + 1
End If
Next
Next
AlleMiUndFrEinesMonats = arr
End Function
Andreas Killer
> Ansatz war den ersten Mi oder aber es kann ja auch ein Fr sein zu
> errechnen und dann entsprechend das Datum plus zwei Tage bzw. plus fünf
> Tage weiter aufrechnen zu lassen. Doch komme ich somit auf max. drei
> Datumswerte im Monat und nicht auf alle.
Hmm? Wieso das? Ist doch genau so völlig richtig.
Also den ersten Mi oder Fr zu rechnen ist klar, denke ich.
Zu diesem Tag dann 2, bzw. 5 addieren und dabei einfach prüfen ob das
resultierende Datum noch in dem Monat des ersten Tages liegt.
Andreas.
Function MiOderFrImMonat(ByVal jahr As Integer)
Dim arr(0 To 8, 11) As Variant
Dim erster As Date
Dim m As Integer, i As Integer
For m = 1 To 12
erster = DateSerial(jahr, m, 1)
arr(0, m - 1) = erster + IIf(erster Mod 7 < 5, 4, 6) - _
erster Mod 7
For i = 1 To 8
If Weekday(arr(i - 1, m - 1)) = vbFriday Then
arr(i, m - 1) = arr(i - 1, m - 1) + 5
Else
arr(i, m - 1) = arr(i - 1, m - 1) + 2
End If
If Month(arr(i, m - 1)) <> Month(arr(0, m - 1)) Then _
arr(i, m - 1) = ""
Next
Next
MiOderFrImMonat = arr
End Function
Berthold Erfämper
> Hallo Peter, hallo Andreas
>
> vielen Dank für die Formel / Funktion.
>
die Funktion einfüge erhalte ich in allen Zellen immer nur den
01.01.2010. Und das war ein Freitag.
???
Berthold
Peter Schleif
>>
> also irgend etwas mache ich verkehrt. Wenn ich wie von Euch beschrieben
> die Funktion einfüge erhalte ich in allen Zellen immer nur den
> 01.01.2010. Und das war ein Freitag.
Hast Du denn die Formel mit <STRG> <SHIFT> <RETURN> abgeschlossen?
Peter
Berthold Erfämper
>
> vielen Dank für die Formel / Funktion.
>
Klappt hervorragend. Genau das was ich gesucht habe.
Vielen, vielen Dank.
Wäre ich alleine nie drauf gekommen.
Berthold
Berthold Erfämper
>
> Also den ersten Mi oder Fr zu rechnen ist klar, denke ich.
> resultierende Datum noch in dem Monat des ersten Tages liegt.
>
> Andreas.
>
> Function MiOderFrImMonat(ByVal jahr As Integer)
> Dim arr(0 To 8, 11) As Variant
> Dim erster As Date
> Dim m As Integer, i As Integer
>
> For m = 1 To 12
> erster = DateSerial(jahr, m, 1)
> arr(0, m - 1) = erster + IIf(erster Mod 7< 5, 4, 6) - _
> erster Mod 7
> For i = 1 To 8
> If Weekday(arr(i - 1, m - 1)) = vbFriday Then
> arr(i, m - 1) = arr(i - 1, m - 1) + 5
> Else
> arr(i, m - 1) = arr(i - 1, m - 1) + 2
> End If
> If Month(arr(i, m - 1))<> Month(arr(0, m - 1)) Then _
> arr(i, m - 1) = ""
> Next
> Next
> MiOderFrImMonat = arr
> End Function
Hallo Andreas,
leider bekomme ich den 31.12.2010 (Freitag) nicht angezeigt / berechnet.
Gruss.
Berthold
Ute Simon
> Jahr. Diese dann in zwölf Spalten in aufsteigender Reihenfolge.
Hallo Betrthold,
müssen die wirklich mit einer Formel berechnet werden oder nur in die
Tabelle eingetragen? Falls letzteres, kommst Du so am schnellsten zum
Ergebnis:
- Trage die beiden Startdaten (01.01.2010 und 06.01.2010) in den beiden
ersten Spalten ein.
- Markiere beide.
- Ziehe am Ausfüllkästchen, um die beiden Zellen auf die nächsten 110
Spalten zu kopieren.
- Es erscheint neben der Markierung ein Ausfüllen-Optionen-Kästchen.
- Wähle dort "Wochentage ausfüllen".
Viele Grüße
Ute
Alexander Wolff
> Die folgenden Datümer eines Monats werden dann über die nächsten 9
> Zeilen mit TT.MM;; oder TT;; formatiert und mit
> A7:
> =(MONAT(A6+2+3*(REST(A6;7)=6))=MONAT(A$6))*(A6+2+3*(REST(A6;7)=6))
a) Bitte korrigieren:
=(MONAT(A6+2+3*(REST(A6;7)=6))=MONAT(A$6))*(A6>=A$6)*(A6+2+3*(REST(A6;7)=6))
b) Peters Arrayfunktion auf 10x12 Einträge erweitert:
------------------------------------------------------------------------
Function MiOderFrImMonat(ByVal jahr As Long, _
Optional align1stFriday As Boolean = False)
'align = WAHR: 1. Freitag vor 1. Mittwoch beginnt erst in Zeile 2
'=> gleiche Wochentage stehen in Monaten nebeneinander
Dim arr(9, 11) As Date
Dim erster As Date
Dim a As Date
Dim m As Long
Dim n As Long
Dim i As Long
For m = 0 To 11
erster = DateSerial(jahr, m + 1, 1)
a = erster + IIf(erster Mod 7 < 5, 4, 6) - erster Mod 7
If a Mod 7 = 6 Then i = -align1stFriday Else i = 0
arr(i, m) = a
For n = 1 + i To 9
arr(n, m) = arr(n - 1, m) + 2 - 3 * (arr(n - 1, m) Mod 7 = 6)
If Month(arr(n, m)) <> Month(a) Or arr(n, m) < a Then arr(n, m) = 0
Next
Next
MiOderFrImMonat = arr
End Function
------------------------------------------------------------------------
(kann bestimmt noch schöner geschrieben werden)
Andreas Killer
> leider bekomme ich den 31.12.2010 (Freitag) nicht angezeigt / berechnet.
Uups. :-) Bitte jetzt nochmal's probieren.
Andreas.
Function MiOderFrImMonat(ByVal jahr As Integer)
Const MaxDay = 9
Dim arr(0 To MaxDay, 11) As Variant
Dim erster As Date
Dim m As Integer, i As Integer, j As Integer
For m = 1 To 12
erster = DateSerial(jahr, m, 1)
arr(0, m - 1) = erster + IIf(erster Mod 7 < 5, 4, 6) - _
erster Mod 7
For i = 1 To MaxDay
If Weekday(arr(i - 1, m - 1)) = vbFriday Then
arr(i, m - 1) = arr(i - 1, m - 1) + 5
Else
arr(i, m - 1) = arr(i - 1, m - 1) + 2
End If
If Month(arr(i, m - 1)) <> Month(arr(0, m - 1)) Then
For j = i To MaxDay
arr(j, m - 1) = ""
Next
Exit For
End If