Zahl am 3 Freitag im letzten Quartalsmonat

[gitmichi]

Hallo,

ich möchte eine Zahl aus einer Liste mit Datumswerten ausgeben.

Genau soll der wert neben dem datum des 3. Freitags im letzten Quartalsmonats Z.B. März ausgegeben werden und genau das selbe am Montag vor dem 3. Freitag. Das kann trickiy sein, da dieser Montag auch schon mal der 2. Montag des Monats sein kann.


eine datei mit der erklärung ist in der anlage!
  Quartalstag.xlsx (Größe: 12,35 KB / Downloads: 19)
Kann mir da jemand helfen?

ein 

Das wäre toll.

LG

[Stoffo]

Hallo,

habe mal eine Spalte zwischen C und D in deiner Beispieldatei eingefügt und exemplarisch für D59 folgende Formel:

Code:

=WENN(UND(WOCHENTAG(C59;2)=5;TAG(C59)<=21;TAG(C59)>14);E59;"")

Sollte für Montag durch einbau von ODER analog anpassbar sein.

[gitmichi]

Hallo,

danke, ich meinte es so das ich in spalte den wert automatisch eingetragen bekomme, wen  excel an hanD  der ganzen datumswerte einen 3. freitag im letzten monat des quartals erkennt?
ich dachte es gibt eine formel die genau das in der jeweiligen zeile erkennt!

[Stoffo]

Hallo,

das ist prinzipiell auch möglich. Da ich aber nicht ersehen kann wie sich deine Werte berechnen müsstest du in meiner Formel E59 durch deine Formel zur Berechnung des jeweiligen Wertes eintragen. Habe übrigens überlesen, dass du die Werte nur einmal im Quartal ermittelst und nicht monatlich den 3. Freitag haben willst. Daher müsste zusätzlich noch Oder(Monat(C59)=3;...6,9,12 ergänzt werden.

[snb]

Code:

Sub M_snb()
  For j = 3 To 12 Step 3
     y = DateSerial(2000, j, 1)
     MsgBox y - Weekday(y, 2) + 15
     MsgBox y - Weekday(y, 2) + 19
  Next
End Sub

[Sigi.21]

Hallo Gitmichi,

zwar mit Formeln, ist aber nicht schön. Geht vllt auch einfacher. (s. Mappe)

Gruß Sigi

[LCohen]

XL2021/365/Web, separat ausgegeben:
=FILTER(C7:D84;(REST(MONAT(C7:C84);3)=0)*(ABS(TAG(C7:C84)-14)<4)*(REST(REST(C7:C84;7);4)=2))

XL2021/365/Web, separat ausgegeben, gekapseltes Argument:
=LET(cd;C7:D84;f;INDEX(cd;;1);FILTER(cd;(REST(MONAT(f);3)=0)*(ABS(TAG(f)-14)<4)*(REST(REST(f;7);4)=2)))

XL2021/365/Web, Ausgabe wie gewünscht:
E7: =D7:D84*(REST(MONAT(C7:C84);3)=0)*(ABS(TAG(C7:C84)-14)<4)*(REST(REST(C7:C84;7);4)=2)

Altes Excel, Ausgabe wie gewünscht (ohne die {} mit Strg-Umsch-Eing auf Markierung E7:E84 abschließen):
{E7:E84}: {=D7:D84*(REST(MONAT(C7:C84);3)=0)*(ABS(TAG(C7:C84)-14)<4)*(REST(REST(C7:C84;7);4)=2)}

Altes Excel, Ausgabe wie gewünscht, klassisch herunterkopiert:
E7[:E84]: =D7*(REST(MONAT(C7);3)=0)*(ABS(TAG(C7)-14)<4)*(REST(REST(C7;7);4)=2)
_________________________________________________________________________
@sigi.21: Es wäre schön, für eine einzelne Formel nicht gleich eine Mappe hochgeladen zu bekommen! Das ist für das Forum CEF noch belastender, als ein Fullquote oder ein Hajo-Textbaustein. Außerdem hemmt es den Lesefluss.

Mein (schon überreichlicher) Text mit Formatierungen: 1.119 Byte, Deine Datei: >13.000 Byte
Extrapoliert auf 530 x sigi.21, 3630 x LCohen: 6,89 MB sigi.21, 4,06 MB LCohen. Du hast mich also speichermäßig in CEF schon überholt, trotz nur 1/7 der Beiträge.

[gitmichi]

Hallo und ein dickes Danke,

ich habe diese Formel hier genommen:

E7[:E84]: =D7*(REST(MONAT(C7);3)=0)*(ABS(TAG(C7)-14)<4)*(REST(REST(C7;7);4)=2)

jedoch wird in manchem Quartal DER 2. Freitag und der 3. Montag genommmen, woran kann das liegen??

LG gitmichi

---

hab mal die Tabelle zur besseren Sichtbarkeit des Problems angehängt, vileicht hilft das weiter..

Danke Euch
  Quartalszahlen 2.xlsx (Größe: 34,88 KB / Downloads: 6)

[snb]

UDF:

Code:

Function F_snb(d)
  If Month(d) Mod 3 = 0 Then
      d0 = d - Day(d) + 1
      If InStr(" 15 19 ", " " & d - (d0 - Weekday(d0, 2)) & " ") Then F_snb = d.Offset(, 1)
  End If
End Function

In E7

PHP-Code:

=F_snb(C7) 


[LCohen]

E7: =D7*ODER((REST(MONAT(C7+{0.4});3)=0)*(ABS(TAG(C7+{0.4})-18)<4)*(REST(C7+{0.4};7)=6))

Das erste +{0.4} kann weg, aber ich lasse es ordnungshalber stehen.

---

Hallo snb, besser ist doch, C7:D7 statt C7 als Argumente zu verwenden, weil Offset eigentlich im Sinne der Kapselung nicht erlaubt ist. Denn eine Funktion "darf nicht wissen müssen", was außerhalb passiert. Eine Spalte D eingefügt und rrrrummmmms.