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!
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 |
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
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 |
Zitat:klar, Du hast 1904er Datumswerte!
falls du damit meinst, dass in meinen Optionen 1904-Datumswert aktiviert ist - nein, ist es nicht.
Zitat: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.
Hi,
Zitat: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)
Zitat:dass in meinen Optionen 1904-Datumswert aktiviert ist - nein
Dann dürfte auch nicht der 28.6. rauskommen!
"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
Zitat: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.
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.