Office: (Office 2013) Aus Text ein Datum erstellen

Hallo,
nachdem ich in der Suche nicht fündig geworden bin, versuche ich es mit dieser Anfrage.
Ich habe das Problem, dass ich in einer Spalte ein Datum im Textformat habe und diese dadurch nicht chronologisch sortieren kann. Daher versuche ich mich an einer Umwandlung mit Hilfe der TEXT/DATWERT/WERT Funktionen und komme nicht mehr weiter.
Der Text ist wie folgt aufgebaut: "MMM DD, YYYY". Insbesondere die ausgeschriebene Monatsangabe macht mir Schwierigkeiten. Beim Sortieren ist dann natürlich April vorne, oder September, je nachdem ob ich absteigend oder aufsteigend sortiere.
Könnt ihr mir helfen daraus ein sabueres, sortierbares Datum zu machen?

[table="width: 500"]
[tr]
[td]Datum[/td]
[/tr]
[tr]
[td]Aug 30, 2018[/td]
[/tr]
[tr]
[td]Sep 29, 2018[/td]
[/tr]
[tr]
[td]Dez 28, 2018[/td]
[/tr]
[/table]

 

Hallo,

falls in A1 dein Datum im Format "MMM DD, YYYY" steht, zB.
Aug 30, 2018
dann lautet die Umwandlungsformel in ein fortlaufendes Datum:
=DATWERT(TEIL(A1;5;2)&"."&LINKS(A1;3)&"."&RECHTS(A1;4))
Dieses kannst du deinen Wünschen entsprechend mit den Excel-Datumsformaten weiter formatieren.

 

Großartig! Vielen Dank für Deine Hilfe!

 

Ich grabe diesen uralten Thread mal aus, vielleicht kann mir ja jemand weiterhelfen?

Die Formel scheint Probleme zu geben, wenn das Tagesdatum einstellig ist?
Bei zweistelligen funktioniert es einwandfrei, obwohl ich die Formel nicht verstehe. Sieht dann z.B. so aus:

Jun 6, 2021 #WERT!
Jun 10, 2021 44357

Wie kann ich machen, dass es den 6. Juni auch richtig erkennt?

 

=DATWERT(WECHSELN(TEIL(A1;4;3);",";)&"."&LINKS(A1;3)&"."&RECHTS(A1;4))

 

super, danke. Das funktioniert...
kann mir jemand die Formel vielleicht sogar noch für "newbies" erklären? Verstehe es irgendwie nicht wie die funktioniert... Danke!

 

Moin Jabba,
erst mal vorab @lupo1: Ich bin immer wieder fasziniert, wie ihr Formelmenschen solche Aufgaben löst. Hohen Respekt!!

Aber mir geht es wie Jabba - ich verstehe die Formeln nicht, weswegen ich VBAstler bleibe. Denn auch wenn man von VBA NOCH(!) keine Ahnung hat, ist der folgende Code (natürlich länger, aber z.B. mit dem Vorteil der Kommentare) erheblich leichter nachzuvollziehen ...

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sDate As String, sDay As String, sMonth As String, sYear As String
Dim dDate As Date
Dim i As Integer, iDate As Integer, iPos As Integer
Dim aMonthName, aMonthNumber 'Array

Cancel = True

aMonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
aMonthNumber = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")

'holt den Text des Doppelklicks in die String-Variable
sDate = Target.Value

'isoliert die Jahreszahl
sYear = Right(sDate, 4)

'übersetzt den Monatsnamen in seine Zahl
sMonth = Left(sDate, 3)
For i = 0 To UBound(aMonthName)
    If aMonthName(i) = sMonth Then
        sMonth = aMonthNumber(i)
    End If
Next i

'isoliert den Tag
iPos = InStr(sDate, ",")
sDay = Mid(sDate, iPos - 2, 2)

If Left(sDay, 1) = " " Then
    sDay = "0" & Right(sDay, 1)
End If

'setzt die Strings zusammen und konvertiert sie zu Datum
sDate = sDay & "." & sMonth & "." & sYear
dDate = CDate(sDate)

Target.Offset(0, 1) = dDate
MsgBox dDate

End Sub

Er gehört in eine Tabelle kopiert und liefert das Ergebnis als MessageBox und in die Zelle rechts neben dem ursprünglichen Text. Jetzt kann man damit weiterrechnen.

Noch'n Vorteil: Man muss keine Formeln ewig weiterkopieren. VBA funktioniert hier auf Doppelklick und lässt sich in Schleifen verwenden.

Grüße

 

Hallo,

die Formel geht zB. von folgenden 2 Datumsangaben (in Text) aus:
1) "Aug 6, 2023"
2) "Aug 26, 2023"

a) Der Formelteil TEIL(A1;4;3) liefert entweder " 6," oder " 26",
wobei in weiterer Folge der Formelteil WECHSELN(TEIL(A1;4;3);",";) ein eventuell enthaltenes Komma entfernt, also entweder " 6" oder " 26" liefert.

b) Der Formelteil LINKS(A1;3) liefert "Aug".

c) Der Formelteil RECHTS(A1;4) liefert "2023".

d) Diese 3 Textwerte aus a), b) und c) werden mit &"."& zweimal durch den Formelteil WECHSELN(TEIL(A1;4;3);",";)&"."&LINKS(A1;3)&"."&RECHTS(A1;4) zu einem Text verkettet
entweder als " 6.Aug.2023" oder als " 26.Aug.2023"

e) Schließlich wird auf den Text aus d) die DATWERT-Funktion angewendet, also entweder DATWERT(" 6.Aug.2023") oder DATWERT("26.Aug.2023"), sodass man die beiden fortlaufenden Datumszahlen 45144 oder 45164 erhält.

f) Verpasst man diesen fortlaufenden Datumszahlen das Format "TTT., TT.MMM.JJJJ" erhält man die beiden Resultate
So., 06.Aug.2023 (für 45144) bzw. Sa., 26.Aug.2023 (für 45164).

 

Super, Vielen Dank für die Erklärung!

 

Moin @Exl121150,
auch ich danke sehr für die Erläuterung und sehe mit Vergnügen, dass die Formel letztlich genau das Gleiche macht, wie mein langatmiger VBA-Code. Schick!

Grüße

 

=--TEIL(WECHSELN(A1;", ";LINKS(A1;3));5;9) ist noch etwas kürzer (nur 2x statt 3x A1 und nur 2 statt 3 Terme, ohne Punkte)
42 statt 70 Zeichen. Und erlaubt nebenbei noch YY statt YYYY (wenn es denn inhaltlich ausreicht und Windows es korrekt wandelt).
____________________________________________________________________
Zusatz: Für deutsch (z.B. "Mrz") oder englisch (z.B. "Mar") als Datum:
=--TEIL(WECHSELN(A1;", ";LINKS(WECHSELN(WECHSELN(WECHSELN(WECHSELN(A1;"y";"i");"ar";"rz");"ct";"kt");"c";"z");3));5;9)

 

Oder:
=--TEIL(WECHSELN(A2;", ";LINKS(WECHSELN(A2;@{"y"\"ar"\"ct"\"c"};@{"i"\"rz"\"kt"\"z"});3));5;9)
Oder:
=MAX(WENNFEHLER(--TEIL(WECHSELN(A2;", ";LINKS(WECHSELN(A2;{"y"\"ar"\"ct"\"c"};{"i"\"rz"\"kt"\"z"});3));5;9);0))

 

Klappt nicht, da Deine Arrays nicht die notwendige Wechseln-Reihenfolge befolgen. Für Nichtschweizer: \ ist dort der Trenner .

 

Funktioniert hier super in meiner Version O365 Niederländisch.
=MAX(WENNFEHLER(--TEIL(WECHSELN(A1;", ";LINKS(WECHSELN(A1;{"y"\"ar"\"ct"\"c"};{"i"\"rz"\"kt"\"z"});3));5;9);0))
Sowie;
=MAX(WENNFEHLER(--TEIL(WECHSELN(A1;", ";LINKS(WECHSELN(A1;{"y";"ar";"ct";"c"};{"i";"rz";"kt";"z"});3));5;9);0)

 

Aha, Niederländer, nicht Schweizer. Dann kommt möglicherweise noch der sprachlich andere Monatsname hinzu.

Bei einem dt.-dt. System: (Win dt.dt., Excel dt.-dt.) mit Deiner =--TEIL(-Formel:
Mar #WERT!
May ok
Oct #WERT!
Dec #WERT!
Mrz ok
Mai ok
Okt ok
Dez ok

Ich sehe gerade, dass Du die Formeln angepasst hat (eine Klammer-zu fehlt). Warum tust Du das, wenn es mit der ersten Formel aus #12 doch angeblich super klappt?