Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 12:41:11
Vielleicht Onur ? Leider werden nicht alle Monate
16.04.2024 12:08:33
Zelle/Spalte bestimmten Wert in Kopfzeile zuordnen
16.04.2024 12:01:17
Dynamische Liste generiert aus mehreren Vorgagen
16.04.2024 11:48:22
Blatt in HTML exportieren wenn Checkbox aktiv ist
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1592to1596
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1592to1596
1592to1596
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Problem: Englisches Datum/Monat subtrahieren

Problem: Englisches Datum/Monat subtrahieren
24.11.2017 09:21:40
Stephan
Hallo Community, ich komme einfach nicht weiter:
Ich möchte zu einem Monat in einer Zelle den Vormonat ermitteln und in eine andere Zelle schreiben - also sozusagen einen Monat subtrahieren. Beispiel: Aus "November 2017" soll "October 2017" ermittelt werden.
Das funktioniert sehr gut mit dieser Formel: =TEXT(DATUM(JAHR($H$2);MONAT($H$2)-1;1);"[$-809]MMMM JJJJ")
Das Problem ist aber, dass ich englische Monatsnamen habe und eine deutsche Excel 2016 Installation. Aus "October 2017" soll also z.B. "September 2017" werden. Und da streikt die Formel, denn die Funktion MONAT() scheint einen deutschen Monatsnamen zu erwarten. Fehlermeldung: #WERT.
Was ist zu tun, damit die Subtraktion eines englischen Monatsnamens mit einem deutschen Excel gelingt ? Beide Monatsnamen liegen in Englisch vor. Einfaches Beispiel der Problemstellung: https://www.herber.de/bbs/user/117882.xlsx
Vielen Dank für Hilfe und Tipps, Stephan

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
WECHSELN
24.11.2017 09:39:08
lupo1
N2: =TEXT(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(F2;
"arch";"ärz");"ry";"r");"y";"i");"ct";"kt");"c";"z");"ne";"ni")-1;"[$-809]MMMM JJJJ")
AW: WECHSELN
24.11.2017 11:47:56
Stephan
Hallo Lupo,
die Formel funktioniert genial sehr gut. Der Vormonat wird problemlos in englischer Sprache ermittelt. Auch wird von December auf January zurückgerechnet.
Leider muss ich das Problem noch um ein Detail erweitern. Ich muss nämlich auch den Vorjahresmonat und den Vorjahresvormonat bestimmen. Lässt sich das durch eine leichte Modifikation der Formel erreichen ?
Ich habe dazu ein Beispiel erstellt: https://www.herber.de/bbs/user/117888.xlsx
1000 Dank und Gruß, Stephan
Anzeige
-365 statt -1
24.11.2017 11:55:35
lupo1
-393 statt -1 für VorJ-VorM
24.11.2017 12:03:56
lupo1
AW: als Datum mit SUCHEN() und VERWEIS() ...
24.11.2017 09:54:41
...
Hallo Stephan,
... so: =("1-"&VERWEIS(9;1/SUCHEN({"an";"eb";"mar";"pr";"ay";"un";"ul";"ug";"ep";"oc";"ov";"ec"};F2);ZEILE(A$1:A$12)))-1 und Zahlenformat MMMM JJJJ zuweisen.
Gruß Werner
.. , - ...
AW: als Datum mit SUCHEN() und VERWEIS() ...
24.11.2017 10:14:50
Günther
Moin Stephan,
in Excel 365 kannst du ganz locker den Weg über Daten | Abrufen und transformieren | Aus Tabelle gehen, da hat Excel einen Automatismus, erkennt das US-Datum und wechselt es in DE-Datum um. Und wenn du dann eine Benutzerdefinierte Spalte mit der Berechnung hinzufügst, hast du auch das gewünschte Ergebnis.
AW: sorry, Formel ist nur im akt. Jahr richtig owT
24.11.2017 14:57:41
...
Gruß Werner
.. , - ...
AW: Problem: Englisches Datum/Monat subtrahieren
24.11.2017 11:43:03
Stephan
Manchmal frage ich mich: wie kommt man nur auf so etwas ? Die Problemlöser sind echt genial. Ich schaffe es noch nicht einmal, die angebotenen Formeln zu 100% zu verstehen. Dennoch:
Die Lösungen sind zwar vielversprechend, aber decken das Problem noch nicht vollständig ab. Neben dem Monat ist auch das Jahr zu berücksichtigen. Zum einem muss aus dem January des aktuellen Jahres in den December des Vorjahres zurückgerechnet werden. Zum anderen benötige ich auch den Vorjahresmonat und den Vorjahresvormonat.
Wie sind die Formeln zu erweitern, damit das Jahr entsprechend berücksichtigt wird ? Ich habe dazu mein Beispiel modifiziert, damit man die Formeln dort ganz einfach ausprobieren kann.
https://www.herber.de/bbs/user/117888.xlsx
Vielen Dank, Stephan
Anzeige
Das zeigt wieder mal, dass sich bedankt wird, aber
24.11.2017 11:46:16
lupo1
... nicht ausprobiert!
neopa's und meine Lösung gehen auf F2 DIREKT los und behandeln es als Datum, nicht als Text. Somit wird auch korrekt ein Monat abgezogen.
AW: Das zeigt wieder mal, dass sich bedankt wird, aber
24.11.2017 12:00:08
Stephan
Hallo Lupo,
hatte Deine Lösung ausprobiert und sogar eine kleine Tabelle dazu generiert. Deine Funktion arbeitet sehr gut ! Das zeigt sich bei meinem Test sehr gut.
Nur weil der Ansatz so ungewohnt ist, schaffe ich es nicht aus eigener Kraft, die Subtraktion eines Jahres hinzuzufügen. Also eine Erweiterung der Problemstellung.
Bei neopa's Lösung dagegen gibt es eine Ungenauigkeit im Jahr. Aus January 2018 wird durch Subtraktion irgendwie December 2016.
Das hatte ich also schon ausprobiert.
Beste Grüße, Stephan
Anzeige
Weil Du wirklich sehr höflich bist: :-)
24.11.2017 12:13:56
lupo1
Siehe oben im Thread die geänderten Abzüge für VorJ-M und VorJ-VorM ggü der -1 für VorM.
Das mit dem Vormonat Dezember 16 funktionierte bei mir von Anfang an.
AW: Weil Du wirklich sehr höflich bist: :-)
24.11.2017 12:20:05
Stephan
Hallo Lupo,
richtig, Dein Jahresumbruch funktionierte von Anfang an sauber. Hatte ich auch so vermerkt. Mir fehlten eben nur noch die -365/- 393. Ich Prinzip hätte ich da auch draufkommen können ... hatte zumindest vermutet, dass es ein einfacher Kniff ist.
1000 Dank, Stephan
AW: Problem: Englisches Datum/Monat subtrahieren
24.11.2017 11:52:17
Günther
Mit meiner angebotenen Lösung gibst du nur die numerische Anzahl von (zu subtrahierenden) Monaten ein, ohne eine ellenlange und nur ab und zu funktionierende Formel. - Da ich es aber nicht wirklich mag, wenn meine Lösungsangebote noch nicht einmal erwähnt werden, verabschiede ich mich aus diesem Thread und überlasse dir die "Fummelei" bzw. die Suche in Sachen Power Query oder mit einer Formel bzw. UDF oder was auch immer ...
Anzeige
AW: Nutzung von PowerQuery
24.11.2017 12:09:52
PowerQuery
Hallo Günther,
sorry, dass ich Deine Lösung bisher noch nicht gewürdigt habe. Aber ich habe das Forum erst vor 15 min wieder betreten. Deine Lösung habe ich sogar als erste probiert, weil ich zunächst auch von den komplexeren Formeln zurückgeschreckt bin.
Was gegen die Lösung mit dem PowerQuery zu sprechen scheint ist, dass ich ein einziges Feld mit einem Datum habe, aus dem ich ein zweites Datum generieren möchte. Über PowerQuery muss ich dazu aus dem Feld eine Tabelle mit einem einzigen Eintrag generieren. Dann muss in einer hinzugefügten Zeile die Berechnung erfolgen und das Ergebnis wird in eine weitere Tabelle geschrieben. Bei jeder neuen Eingabe eines Datum muss dann die Abfrage aktualisiert werden.
Ich bin großer Fan von PowerQueries und hab's probiert, aber es passt leider nicht so gut in meinen Kontext.
Vielen Dank für Deinen Vorschlag !
Grüße, Stephan
Anzeige
AW: Problem gelöst !
24.11.2017 12:16:28
Stephan
Vielen Dank an alle, die sich Gedanken zu meinem Problem gemacht haben !
Mein Tag ist damit sozusagen gerettet !
Beste Grüße, Stephan
2 Anm.
24.11.2017 12:19:20
lupo1
1. gelöst aufgrund welchen Ansatzes?
2. gelöst - und auch verstanden?
AW: 2 Anm.
24.11.2017 12:26:18
Stephan
Ok ;)
Ich habe Deinen Ansatz, Lupo1, verwendet:
=TEXT(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN($B$15;
"arch";"ärz");"ry";"r");"y";"i");"ct";"kt");"c";"z");"ne";"ni")-365;"[$-809]MMMM JJJJ")
Warum ich je nach Problemstellung 1, 365 und 393 subtrahieren muss, ist mir klar. Über den Rest habe ich eine difuse Vorstellung. Durch das mehrfache WECHSELN werden die englischen Monatsnamen eingedeutscht, dann wird subtrahiert und das Ergebnis wieder in den englische Formatierung $-809 übertragen.
Die Formel zu bauen war also auch eine Fleißaufgabe ... Dankeschön !!!
Anzeige
Anm. für MMM statt MMMM
24.11.2017 12:51:13
lupo1
=TEXT(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(F2;
"arch";"ärz");"Mar";"Mrz");"ry";"r");"y";"i");"ec";"ez");"c";"k");"ne";"ni")-1;"[$-809]MMMM JJJJ")
erlaubt die Verwendung gleichermaßen für
MMMM JJ[JJ] und
MMM JJ[JJ] - ist dann im Hinterteil der Formel manuell zu regeln
eine geht noch
24.11.2017 12:43:49
Rudi
Hallo,
der Vormonat:
=DATUM(--RECHTS(A1;4);SUCHEN(LINKS(A1;3);" janfebmaraprmayjunjulaugsepoctnovdec")/3-1;1)
und als MMMM JJJJ formatieren.
Gruß
Rudi
AW: genau die fette -1 ist aber zu viel owT
24.11.2017 15:01:09
...
Gruß Werner
.. , - ...
Niemand, auch Rudi nicht, hat hier den einfachen …
24.11.2017 14:25:45
Luc:-?
…Weg über TEXT mit LänderCode und anschld VERGLEICH benutzt (kurze Fml!), obwohl der schon x-mal von anderen und mir gezeigt wurde! :-|
Und dabei behaupten doch alle Helfer immer wieder, dass sie hier ebenfalls lernen wollen, was damit wohl ad absurdum geführt wurde… :->
Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
zeichma ;)
24.11.2017 14:44:22
lupo1
"TEXT-in-englisch" haben wir schon drin. Wie bitte geht "TEXT-aus-englisch"? Und wenn es geht: Warum dann noch VERGLEICH?
Anmerkung: Bin grottenschlecht im Recherchieren.
Da hatte Rudi das noch drauf! ;-] owT
24.11.2017 16:42:51
Luc:-?
:-?
Na, so, ...
24.11.2017 17:42:50
Luc:-?
…Lupo;
aus …
October 2017
November 2017
December 2017
January 2018
February 2018
…wird …
September 2017
October 2017
November 2017
December 2017
January 2018
…mit dieser (singularen Matrix-)Fml: (Mit AGGREGAT würde es auch eine wdn, Werner! ;-])
N2[:N6]: {=TEXT(("1-"&VERGLEICH(LINKS(F2;LÄNGE(F2)-5);TEXT(DATWERT(WECHSELN("1.#.2017";"#";SPALTE(A:L)));"[$-809]MMMM");0)
&"-"&RECHTS(F2;4))-1;"[$-809]MMMM JJJJ")}
Diese Fml ist natürlich auch etwas länger, kann aber auf diese sprachspezifischen TextKonstanten verzichten und ist somit so uni­ver­sell wie Xl selbst (für eine andere Sprache muss man nur den LandesCode wechseln!).
Übrigens könntest Du daher auch die Schreibung N2[:N6]: für eine ErgebnisBereichsAngabe haben, für nur deren 1.Zelle die rela­tiven Adressen in der Fml gelten. Das käme bei singularen Matrix- und normalen Fmln vor. Ich erzeuge die natürlich mit UDFs, hier bspw mit dieser Fml: =CFormA("*"&RAddress(N2:N6;;;;-1);1)
Gruß, Luc :-?
Anzeige
AW: geht aber auch ohne {} und trodem kürzer ...
25.11.2017 09:36:43
...
Hallo Luc,
... auch ohne AGGREGAT() aber mit Hilfe von INDEX() in einer Matrixfunktion(alität)sformel.
Wobei ich Deinen Ansatz ohne sprachspezifischen TextKonstanten zu arbeiten im Prinzip übernehme, weil er dadurch wie Du schon schreibst universeller eingesetzt werden kann.
=TEXT(("1-"&VERGLEICH(LINKS(A1;3);INDEX(TEXT(--(WECHSELN("1.#";"#";SPALTE(A:L)));"[$-809]MMM"););0)&"-"&RECHTS(A1;4))-1;"[$-809]MMMM JJJJ")
Gruß Werner
.. , - ...
AW: geht natürlich auch hier ohne TEXT() ...
25.11.2017 10:00:44
...
Hallo,
... dann so:
=("1-"&VERGLEICH(LINKS(A1;3);INDEX(TEXT(--(WECHSELN("1.#";"#";SPALTE(A:L)));"[$-809]MMM"););0)&"-"&RECHTS(A1;4))-1
und Zahlenformat: [$-809]MMMM JJJJ
Gruß Werner
.. , - ...
Anzeige
Letzteres ist natürlich klar und hängt ganz ...
26.11.2017 01:25:14
Luc:-?
…davon ab, was der Nutzer erreichen will, Werner;
ansonsten sind Deine Kürzungen OK und könnten auch noch ein ()-Paar und eine 0 umfassen. Wenn ich die in meine Fml über­nehme, ergibt sich die Differenz geschriebener Längen nur noch aus Deinem zusätzlichen INDEX(;), an dessen Wirkung hierauf ich nicht gedacht hatte (verlängert die Fml, ebenso wie sonst AGGREGAT, ja auch nur unnötig ;-]).
Allerdings wäre die Fml dann ggf nicht mehr so universell, weil es ja 3buchstabige Sonderkürzel in anderen Sprachen geben könnte oder solche völlig bzw mitunter unüblich wären (dann sollte MMM aber MMMM wirkungsgleich sein).
SchöSo! Morrn, Luc :-?
AW: in Rudi´s Ansatz lässt sich viel kürzen ...
24.11.2017 15:04:04
...
Hallo Stephan,
... so: =DATUM(--RECHTS(A1;4);SUCHEN(TEIL(A1;2;2);"anebarprayunulugepctovec")/2;1)
Gruß Werner
.. , - ...
Und um beim TEXT zu bleiben ...
24.11.2017 15:18:13
lupo1
=TEXT((SUCHEN(TEIL(A1;2;2);" anebarprayunulugepctovec")/2&-RECHTS(A1;4))-1;"[$-809]MMMM JJJJ")

(schön nachgedacht, neopa, mit 2./3. Stelle)
AW: ach ja, es soll ja wieder engl. Text werden...
24.11.2017 15:39:49
...
Hallo,
... Stephans Satz: "Aus" November 2017" soll "October 2017" ermittelt werden" hatte ich auch schon heute Vormittag überlesen. Dabei wunderte ich mich noch, warum der Ländercode zum Einsatz kam, wird doch "September" auch ohne diesen erzeugt ;-(
Gruß Werner
.. , - ...
AW: es braucht TEXT() doch nicht ...
25.11.2017 09:25:29
...
Hallo @all,
... ich bleibe bei meiner verkürzten Variante von Rudi Formelansatz:
=DATUM(--RECHTS(A1;4);SUCHEN(TEIL(A1;2;2);"anebarprayunulugepctovec")/2;1)
(zu beachten ist, dass darin die Konstante ohne Leerzeichen beginnt) und weise dieser Ergebniszelle als benutzerdefiniertes Zahlenformat einfach: [$-809]MMMM JJJJ zu.
Hat zusätzlich den Vorteil, man kann mit dem Datumswert gleich rechnen.
Gruß Werner
.. , - ...
Das ist Geschmackssache, und sicherlich hast Du
25.11.2017 09:58:34
lupo1
... gerade beim Datum damit recht, weil Datum ja direkt Zahl ist, ohne Frills 'n Thrills. Und somit ist Deine Formel tatsächlich schneller, schöner und kürzer. Und besser.
Ordnungspolitisch (keine unterschiedlichen Typen!) oder manchmal faktisch gibt es aber auch Gründe für TEXT.
Und vielleicht kann nicht jede fremde TK fremdsprachige Zahlenformate (oder nur: Monatsnamen) anzeigen. Aktiv fällt mir da aber gerade keine ein. Dem entgegenhalten könnte man aus Deiner Sicht allerdings wiederum: "Eine solche hypothetische TK könnte ja evtl. aus Okt17 genauso nicht automatisch den 1.10.17 machen, wie Excel es kann." Dann wäre die Formel eh hinfällig. Und damit hättest Du dann auch recht.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige