Datum letzter Freitag im Quartal

[Jockel]

Moin,
...
Auch wenn Power Query hier "overdressed" wäre und eine reine Formel-Lösung gewiss besser ist ...

Moin Günther, nachdem ich das gestern (na gut heute Nacht) nachgebaut und nachvollzogen habe und um eine gewisse Flexibilität (Auswahl eines Wochentags im TabellenBlatt) erweitert habe, bin ich da anderer Meinung... Der code sieht zwar auf den ersten Blick viel aus, aber zu schreiben ist eher wenig... ... Ich habe im Übrigen experimentiert und nicht dein Code Schritt für Schritt "kopiert"...

Ich bin g'spannt ob sich Ralf (Rabe) nochmal hierher verirrt... ... wegen eines Feedback's - auch des Formelangebots wegen... Da kam ja doch Einiges zusammen...

[Rabe]

Hi,

klar bin ich noch da, wenn auch seltener momentan.

Ich hatte eine Spalte, in der sowohl Daten eingetragen wurden, als auch JJ/KW-Zahlen, als auch Qx/JJ.
Für eine einfachere Gruppierung für Pivot wollte ich diese Werte alle in richtige Datums-Zahlen gewechselt haben, mit der Verpflichtung, zukünftig nur noch ein richtiges Datum zu verwenden. Dies war also nur ein einmaliger Akt.

Dazu habe ich erst die KW-Zahlen mit diesen Formeln in Werte umgewandelt:
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=186

Verwendet habe ich dann für das Quartal letztendlich die Formeln in X:

Arbeitsblatt mit dem Namen 'E_Entwicklung'
	R	S	X	Y
8	Q2/2004	2004	25.06.2004	25.06.2004
9	Q2/04	2004	25.06.2004	24.06.1904

Zelle	Formel
S8	=WENN(LINKS([@Spalte1];1)="Q";20&RECHTS([@Spalte1];2);"")
X8	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Y8	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
S9	=WENN(LINKS([@Spalte1];1)="Q";20&RECHTS([@Spalte1];2);"")
X9	=7*KÜRZEN(DATUM(Tabelle1[@Spalte2];TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Y9	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Jockel]

Hi Ralf, das ist freilich deine Entscheidung... Hast du in der X-Formel irgendwo auch die Spalte S drin..? Du beachtest falsche Ergebnisse nicht. Na gut, vielleicht brauchst du ja nicht alle...

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C
1	Quartal	Formel1	Formel2
2	Q1/02	28.03.1902	29.03.2002
3	Q3/2057	28.09.2057	28.09.2057
4	Q3/2005	23.09.2005	30.09.2005
5	Q4/68	27.12.1968	27.12.1968
6	Q3/2068	28.09.2068	28.09.2068
7	Q4/99	24.12.1999	31.12.1999
8	Q4/18	27.12.1918	28.12.2018

Zelle	Formel
B2	=7*KÜRZEN(DATUM(TEIL([@Quartal];SUCHEN("/";[@Quartal])+1;4);TEIL([@Quartal];2;1)*3+1;0)/7)-1
C2	=KÜRZEN(((MONATSENDE(ERSETZEN(A2;1;2;1&"/"&TEIL(A2;2;1)*3);0)+1)/7))*7-1

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Aber is ja auch egal... Bis die Tage

[Rabe]

Hi Jörg,

ja, ich hatte nur Daten > 2000 in der Liste.

Ich habe jetzt mal die Daten hochgeschoben, damit ich die Köpfe zeigen kann.

Arbeitsblatt mit dem Namen 'E_Entwicklung'
	R	S	T	X	Y
4	Spalte1	Spalte2	Spalte3	Q-Freitag
5	Q2/2004	2004	2	25.06.2004	25.06.2004
6	Q2/04	2004	2	24.06.1904	25.06.2004

Zelle	Formel
S5	=WENN(LINKS([@Spalte1];1)="Q";20&RECHTS([@Spalte1];2);"")
T5	=WENN(LINKS([@Spalte1];1)="Q";TEIL([@Spalte1];2;1))
X5	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Y5	=7*KÜRZEN(DATUM(Tabelle1[@Spalte2];TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
S6	=WENN(LINKS([@Spalte1];1)="Q";20&RECHTS([@Spalte1];2);"")
T6	=WENN(LINKS([@Spalte1];1)="Q";TEIL([@Spalte1];2;1))
X6	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Y6	=7*KÜRZEN(DATUM(Tabelle1[@Spalte2];TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Jockel]

Hallo Ralf, ich verstehe, du brauchst halt nur einpaar richtige Werte... Bei meinem Vorschlag brauchst du nicht zaubern; keine Hilfsspalten und sie hat die Excel-Logik mit dem Jahrhundertwechsel "eingebaut"... Aber auch hier: Egal. Du wirst wissen, was wichtig ist und was du vernachlässigen kannst...  

Arbeitsblatt mit dem Namen 'E_Entwicklung'
	R	S	T	U	V	W	X	Y	Z
4	Spalte1	Spalte2	Spalte3				Q-Freitag
5	Q2/2004	2004	2				25.06.2004	25.06.2004	25.06.2004
6	Q2/04	2004	2				24.06.1904	25.06.2004	25.06.2004
7	Q1/02	2002	1				28.03.1902	29.03.2002	29.03.2002
8	Q3/2057	2057	3				28.09.2057	28.09.2057	28.09.2057
9	Q3/2005	2005	3				23.09.2005	23.09.2005	30.09.2005
10	Q4/68	2068	4				27.12.1968	28.12.2068	27.12.1968
11	Q3/2068	2068	3				28.09.2068	28.09.2068	28.09.2068
12	Q4/99	2099	4				24.12.1999	25.12.2099	31.12.1999
13	Q4/18	2018	4				27.12.1918	28.12.2018	28.12.2018

Zelle	Formel
S5	=WENN(LINKS([@Spalte1];1)="Q";20&RECHTS([@Spalte1];2);"")
T5	=WENN(LINKS([@Spalte1];1)="Q";TEIL([@Spalte1];2;1))
X5	=7*KÜRZEN(DATUM(TEIL(Tabelle1[@Spalte1];SUCHEN("/";Tabelle1[@Spalte1])+1;4);TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Y5	=7*KÜRZEN(DATUM(Tabelle1[@Spalte2];TEIL(Tabelle1[@Spalte1];2;1)*3+1;0)/7)-1
Z5	=KÜRZEN(((MONATSENDE(ERSETZEN(R5;1;2;1&"/"&TEIL(R5;2;1)*3);0)+1)/7))*7-1

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Rabe]

Hi Jörg,

Du wirst wissen, was wichtig ist und was du vernachlässigen kannst...  

stimmt, ich dachte mir, nach bis zu 18 Jahren interessiert es niemand mehr, ob das Beginn-Datum für ein kleines Projekt um max. eine Woche daneben liegt. :21:

[Jockel]

Hi Jörg,
stimmt, ich dachte mir, nach bis zu 18 Jahren interessiert es niemand mehr, ob das Beginn-Datum für ein kleines Projekt um max. eine Woche daneben liegt.  :21:

Obwohl du eine funktionierende Lösung angeboten bekommst. Okay, Mut zur Lücke...

[Rabe]

Hi Jörg,

es waren 2,6% der Daten, die mit Q notiert waren und ich hatte die Originale schon mit den Tagesdaten überschrieben. Für nächstes Mal verwende ich die funktionierende Lösung.