Datum letzter Freitag im Quartal

[Rabe]

Hi,

wie bekomme ich das Datum des letzten Freitags im Quartal raus, wenn ich folgenden Zelleintrag habe:
Q1/2004
oder
Q1/04

Danke für eine Formel!

[BoskoBiati]

Hi,


http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=176, das Quartal kannst Du ja selbst auflösen.

[WillWissen]

Hi Ralf,

ich gehe davon aus, dass deine Quartalsangaben Text sind. Daher arbeite ich (wie fast immer= mit Hifsspalte). In diese schreibst du händisch den letzten des jeweiligen Quartals.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	30. Sep	28.09.2018	Freitag

Zelle	Format	Wert
A1	TT. MMM	30.09.2018
B1	TT.MM.JJJJ	28.09.2018
C1	TTTT	28.09.2018

Zelle	Formel
B1	=DATUM(JAHR(A1);AUFRUNDEN(MONAT(A1)/3;0)*3+1;0)+(WOCHENTAG(A1)>5)*7-WOCHENTAG(A1)+5-6
C1	=B1

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

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

[BoskoBiati]

Hi,

das geht auch so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
7	Q1/2004	Fr 26.03.04
8	Q1/04	Fr 25.03.04

Zelle	Formel
B7	=7*KÜRZEN(DATUM(TEIL(A7;SUCHEN("/";A7)+1;4);TEIL(A7;2;1)*3+1;0)/7)-1
B8	=7*KÜRZEN(DATUM(TEIL(A8;SUCHEN("/";A8)+1;4);TEIL(A8;2;1)*3+1;0)/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

[MisterBurns]

Ich würde ebenfalls mittels Hilfsspalte erstmal die Quartalsschreibweise in ein Datum umwandeln:

Code:

=DATWERT(WENN(LINKS(A1;2)="Q1";"01.03.";WENN(LINKS(A1;2)="Q2";"01.06.";WENN(LINKS(A1;2)="Q3";"01.09.";"")))&WENN(LÄNGE(A1)=7;RECHTS(A1;4);RECHTS(A1;2)))

Und dann mit der von Bosko verwiesenen Formel

Code:

=7*KÜRZEN(DATUM(JAHR(B1);MONAT(B1)+1;6-5)/7)+5-6

Ginge natürlich auch in einer Formel, würde dann aber mE extrem unübersichtlich und nicht nachvollziehbar.

@Edgar: Bei deiner letzten Formel - die äußerst elegant ist - kommt ein falscher Tag heraus :) Beispiel Q2/2018 erhalte ich 28.06.1918

[BoskoBiati]

Hi Berni,


klar, Du hast 1904er Datumswerte!

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
7	Q2/2018	Fr 29.06.18

Zelle	Formel
B7	=7*KÜRZEN(DATUM(TEIL(A7;SUCHEN("/";A7)+1;4);TEIL(A7;2;1)*3+1;0)/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

Im übrigen ist die Formel für die Monatsbestimmung ja sowas von überzogen!

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	Q1/2018	01.03.18
2		01.03.18

Zelle	Formel
B1	=DATWERT(WENN(LINKS(A1;2)="Q1";"01.03.";WENN(LINKS(A1;2)="Q2";"01.06.";WENN(LINKS(A1;2)="Q3";"01.09.";"")))&WENN(LÄNGE(A1)=7;RECHTS(A1;4);RECHTS(A1;2)))
B2	=--("1."&TEIL(A1;2;1)*3&"."&RECHTS(A1;2))

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

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

[MisterBurns]

klar, Du hast 1904er Datumswerte!

falls du damit meinst, dass in meinen Optionen 1904-Datumswert aktiviert ist - nein, ist es nicht.

Im übrigen ist die Formel für die Monatsbestimmung ja sowas von überzogen!

Das mag sein, ich kann es leider nicht besser. Ich bin kein Formelgott und versuche einfach nur, eine Lösung anzubieten.
Dieser Punkt stört mich sowieso in den Foren, dass nicht ganz so perfekte Lösungen immer wieder abgetan werden. Jeder versucht im Rahmen seiner Möglichkeiten einen Beitrag zu leisten.

[BoskoBiati]

Hi,

Dieser Punkt stört mich sowieso in den Foren, dass nicht ganz so perfekte Lösungen immer wieder abgetan werden. 

Die Lösung wurde nicht abgetan, allerdings waren bereits zwei einfachere Lösungen da. Ich halte nicht viel von einer Aneinanderreihung von WENN, da das sehr viel fehleranfälliger ist und in vielen Fällen nicht durchdacht.

Es gäbe zudem noch mehr Möglichkeiten:

Code:

=Datum(rechts(a1;2);wahl(teil(a1;2;1);3;6;9;12);1)
=Datum(rechts(a1;2);teil(a1;2;1);1)

dass in meinen Optionen 1904-Datumswert aktiviert ist - nein

Dann dürfte auch nicht der 28.6. rauskommen!

[MisterBurns]

"Halte ich für sowas von überzogen" empfinde ich aber schon so, dass es leicht überheblich wirkt. Und zusätzliche Lösungen anzubieten, kann wohl kein Fehler sein. Aber egal, das soll hier keine Diskussion werden.

Und

Dann dürfte auch nicht der 28.6. rauskommen!

...du weißt ja, wie das mit sollte/hätte/könnte/dürfte ist. Fakt ist, dass die Option bei mir nicht aktiviert ist und dennoch der 28.06.1918 bei mir angezeigt wird.

[BoskoBiati]

Hi Berni,


ok, dann ist aber etwas an Deinen Einstellungen faul, weil mein Excel mir keine Datumswerte anzeigt, die 100Jahre zurückliegen, wenn ich es nicht explicit vorgebe:

04--> 2004
18--> 2018
1918--> 1918

Außerdem ist meine Formel ja wohl nicht extrem unübersichtlich und nicht nachvollziehbar.