Aufgabe (mit 4 Lösungen):

Führe innerhalb eines Datumsintervalls alle Datümer "Freitag, der 13." auf!

Lösung 1 - eine nur testende und damit langsame Matrixformel:

A1: 01.12.2080 (als Beispiel für das Intervall-Ende)
A2: 6 (steht für Freitag; Samstag beginnt wieder bei 0)
A3: 13 (der gesuchte 13. des Monats)

C1: 01.01.2007 (Intervall-Beginn)
C2: {=(C1<$A$1)*(C1>=$C$1)*MIN(-WECHSELN("-"&(C1+ZEILE($1:$609))*(REST(C1
+ZEILE($1:$609);7)=$A$2)*(TAG(C1+ZEILE($1:$609))=$A$3);"-0";-99999))}

C2 ist eine Matrixformel, die ohne die {} mit Strg-Umsch-Eingabe eingegeben wird, worauf die {} kennzeichnend in der Zelle erscheinen.

C2 kann nun nach Belieben herunterkopiert werden; für das Beispielsintervall genügt ein Ausfüllen bis C130. 

Diese Lösung benötigt viel Rechenleistung (609*129 Formeln), da das Konstrukt ZEILE(1:609) starr 609 Durchläufe unternimmt. Eine VBA-Lösung wäre hier etwas im Vorteil, da die Funktion schon bei erster Übereinstimmung beendet würde. Der Vorteil läge jedoch nur im Bereich etwa doppelter Geschwindigkeit, da durchschnittlich bei der Hälfte beendet würde, und würde aufgrund bloßer Verwendung von VBA noch etwas gemindert.

Übrigens: ZEILE(1:609) kann bei einer Folgeformel auf ZEILE(28:609) beschleunigt werden, da das NÄCHSTE Datum zu einem ZUTREFFENDEN Datum mindestens 4 Wochen später liegt.

Lösung 2 - rechnend, schnell, jedoch beschränkt auf die Tage 1-28 eines Monats: (verkürzt am 2007.04.26)

Unter Zuhilfenahme der Erkenntnis von 0028.htm über die wochentagsgleichen Monate ergibt sich aber auch eine andere Möglichkeit, die nicht schleifenseitig testet, sondern sofort berechnet:

Dekodiert man die Zeichenfolge "jckgmnuqlptrdhkgmnrvluqo" mit =CODE()-96, ergibt sich für jeden Monat in den ersten 12 Zeichen sein wochentagsgleicher Folgemonat in normalen Jahren. Bei Schaltjahren gelten entsprechend die zweiten 12 Zeichen. 

C3: =DATUM(JAHR(C2);CODE(TEIL("jckgmnuqlptrdhkgmnrvluqo";MONAT(C2)+REST(SUMMENPRODUKT(
--(REST(JAHR(C2)+(ODER(MONAT(C2)={7;8;10;11;12}));{400;100;4})=0));2)*12;1))-96;TAG(C2))

Dies gilt nur für die Datumstage 1-28 eines Monats; für 1-31 siehe VBA-Lösung 3 oder die rechnende Lösung 4.

Achtung: Die bis Excel 2003 erlaubten 8 verschachtelten Funktionen sind damit gerade erreicht; bei weiterer Verschachtelung müsste mittels Einfügen Namen Definieren ein Teil ausgegliedert werden: =DATUM(...;CODE(TEIL(...;...+REST(SUMMENPRODUKT(--(REST(...+(ODER(MONAT(...)=...));...)=...));...)*...;...))-...;...)

Erläuterung der Unterformel:

REST(SUMMENPRODUKT(--(REST(JAHR(C2);{400;100;4})=0));2) ermittelt, ob das Datum C2 in einem Schaltjahr liegt. *12 positioniert entsprechend im o.g. String.

(ODER(MONAT(C2)={7;8;10;11;12})) korrigiert die Formel insoweit, als dass sie nun nicht mehr ausdrückt, dass C2 ein Schaltjahr ist, sondern dass für den wochentagsgleichen Folgemonat von C2 der Folgejahr-Schalttag übersprungen wird.

Die Formel in C3 wird nun herunterkopiert; sie kann noch um einen Faktor wie (C1<$A$1)*(C1>=$C$1) aus Lösung 1 erweitert werden. C2 (Lösung 1) wird trotzdem weiter benötigt, da es die Formel C3 (Lösung 2) überhaupt erst auf die beiden Parameter A2 und A3 einstellt. Anders gesagt: C3 nimmt auf die Parameter A1,A2,A3,C1 direkt keinen Bezug mehr.

Der Geschwindigkeitsvorteil von Lösung 2 beträgt nun 1 oder 2 Größenordnungen!

Lösung 3 - VBA, rechnend, für alle Tage 1-31, schnell:

Die folgende Funktion nimmt auch die Tage 29-31 auf. Außerdem kann mit dem optionalen Argument eine Zahl von auszulassenden Treffern bestimmt werden: Beträgt es 9, wird erst das 10. zutreffende Datum gezeigt. Dies geschieht rekursiv.

Function fdwDat(Datum As Long, _
       Optional Multi As Long) As Long
           
'-^-- bedeutet: folgendes datumstags- und wochentagsgleiches Datum
'#28.07.2007# = fdwDWDat(#28.04.2007#)
'#28.06.2008# = fdwDWDat(#28.04.2007#, 1) <= übersprungene Treffer=1

Dim d(2): d(0) = 400: d(1) = 100: d(2) = 4
Mehr = (Multi <> 0)
Tag = Day(Datum)
Mo1 = Datum - Tag + 1 'verwendet wird Monatserster statt Datum

Do
  
  '-v-- Ermittlung, ob die zweiten 12 Codes anzuwenden sind--v-
  FolgeJ = Year(Mo1) - (Month(Mo1) > 6 And Month(Mo1) <> 9)
  
  '-v-- Ermittlung, ob im Datumssprung ein Schalttag liegt --v-
  SchTag = 0
  For i = 0 To 2
    SchTag = SchTag - ((FolgeJ Mod d(i)) = 0)
  Next
  SchTag = (SchTag Mod 2) * 12
  
  Mo1 = DateSerial(Year(Mo1), Asc(Mid("jckgmnuqlptrdhkgmnrvluqo", _
        Month(Mo1) + SchTag, 1)) - 96, Day(Mo1))
  
Loop Until Month(Mo1) = Month(Mo1 + Tag - 1) 'gibt es den Tag?

fdwDat = Mo1 + Tag - 1

'-v-- Rekursion für das n-te Datum
If Mehr Then fdwDat = fdwDat(Mo1 + Tag - 1, Multi - 1)

End Function

Auch diese Funktion benötigt die Lösung 1 in C2. Das (optionale) Argument "Multi" - im Code erläutert - könnte noch dahingehend geändert werden, dass gewünschter Wochentag und Datumstag auch direkt in die Funktion eingehen.

Lösung 4 - rechnend, schnell, für alle Tage 1-31: (gefunden am 2007.05.07)

=C2+(CODE(TEIL(WENN(TAG(C2)=TAG(C2+(CODE(TEIL(
"J'F0FF`J0=J=J'F0FJ`J0=J=J'F0FJ`J0=J=J'm0FJmJ0JJd";
MONAT(C2)+MAX(TAG(C2)-28;)*12;1))-35)*7);
"J'F0FF`J0=J=J'F0FJ`J0=J=J'F0FJ`J0=J=J'm0FJmJ0JJd";
"0=F0FFS`0S=00=F0FFS`0S=00=F0F`S`0S=0==`0F`z`0`=z");
MONAT(C2)+MAX(TAG(C2)-28;)*12;1))-35)*7
-UND(TAG(C2+{0;366})=31)*(MONAT(C2)=12)*518

Diese Lösung beinhaltet folgende Überlegungen: Ein sich wiederholendes Doppelmerkmal Wochen-/Datumstag ist vom Vorgänger C2 genau 4, 13, 26, 35, 39, 48, 61, 65, 74 oder 87 ganze Wochen entfernt, umcodiert als Zeichen '0=FJS`dmz. Diese lassen sich durch den unteren 7bit ASCII-Zeichensatz (ohne die ersten 32 Zeichen) international gültig mit einer Zeichenlänge darstellen, wenn man 35 addiert. Mit 7 multipliziert ergeben sich daraus die zum Vorgänger einfach zu addierenden Tage.

Die beiden 48 Zeichen langen Codeketten (für normales und Schaltjahr) bestehen aus den jeweils 12 Zeichen langen Abschnitten für die 4 Fallbetrachtungen Tag 1-28, 29, 30 und 31. Der 31.12. kennt dabei sogar 3 verschiedene Zustände: 91, 455 und 609 Tage (13, 65 und 87 Wochen) bis zum Nachfolger. Diese lauten: "Folgejahr 1 = Schaltjahr, Folgejahr 2 = Schaltjahr, keines von beiden ein Schaltjahr". In der Formel wird diese Besonderheit ab der letzten 7 wiedergegeben.

Die Formel gibt also die richtige von grundsätzlich 97 verschiedenen Möglichkeiten für einen Nachfolger zurück, ist dabei aber nur 323 Zeichen lang.

Achtung: C2 darf nicht früher als 1.3.1900 sein, da Excel 1900 nicht zutreffend als Schaltjahr behandelt.