Konvertieren Sie verschiedene Texteingaben in Excel in das Datum
Ich habe eine Reihe der in StackExchange vorgeschlagenen Lösungen für die Konvertierung von Text in Excel auf den neuesten Stand gebracht. Keine dieser Lösungen funktioniert jedoch zuverlässig für die Daten, die ich habe. Weitere Informationen finden Sie weiter unten.
Ich habe eine Spalte mit einem "Startdatum". Es enthält Datum in den folgenden Formen:
(Startstatus) 17.09.2019 09:30:59 20.10.2020 16:45:59 26.02.2020 12:20:59 Uhr
Dieses Bild zeigt Daten und den gewünschten Endzustand ...
Ich möchte den gesamten Eintrag in einen Excel-Datumswert konvertieren, damit ich nützliche Berechnungen durchführen kann, z. B. eine Startzeit am z. B. 20.11.2020 von einer Endzeit am 21.11.2020 subtrahieren, z. B. z. B. die zweite Eintrag oben als (vorzugsweise) 23/02/2020 20:31:59 (weil ich ein Brite bin), aber selbst wenn ich es auf die US-Version bringen kann, ist das in Ordnung, ich kann damit arbeiten.
(Gewünschter Endzustand) 17/09/2019 09:30 20/10/2020 16:45 26/02/2020 12:20
Bitte zeigen Sie mich nicht auf andere Antworten, die dies zu lösen scheinen. Ich habe versucht, sie zu benutzen. Jeder scheint ein Problem zu haben, und ich kann nicht sehen, wie dies behoben werden kann, oder ich hätte diese Frage nicht gestellt.
zB Wenn ich es versuche =datevalue(left(a1,9)), werden keine Einträge mit einem zweistelligen Monat (dh 10, 11, 12) angezeigt.
Wenn ich das =date(mid.... etcGleiche versuche , werden nicht alle Informationen zuverlässig erfasst, da die verschiedenen Zellen unterschiedliche Informationslängen aufweisen.
Ich habe die Text-To-Columns- Methode verwendet, die sie in Zellen ohne Datums- / Zeitformat aufteilt und die PM oder AM in eine andere Spalte abspaltet, die ich verwerfen kann, ABER dies bedeutet, dass ich anstelle von 20:31:59 bekomme 08:31:59 in einer Spalte und PM in einer anderen, und ich kann nicht sehen, wie ich dies dazu bringen kann, 20:31:59 zurückzukehren.
Wenn etwas in dieser Frage nicht klar ist, fragen Sie bitte.
Und wenn ich etwas wirklich Offensichtliches verpasst habe, entschuldige ich mich, aber ich habe Stunden damit verbracht, dies zu lösen.
Antworten
Mein erster Versuch wäre, ein benutzerdefiniertes Datumsformat in der Textzelle abzugleichen. Wenn dies funktioniert, wird der Text in der Zelle um ein Leerzeichen eingerückt und Sie können = A1 + 0 sagen, um dem Datum null Tage hinzuzufügen, ohne einen Fehler auszulösen. Formatieren Sie dann die neue Zelle nach Ihren Wünschen neu.
Im schlimmsten Fall können Sie die Zeichenfolge in ein natives Datumsformat zerlegen, das Excel versteht. Sie benötigen keine festen Längen, können aber das Leerzeichen und / oder die Zeichen als Trennzeichen verwenden, z. B. um den Tag zwischen den beiden zu erhalten
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
Was semantisch bedeutet
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
Geben oder nehmen Sie eine +1 oder -1. Ich bin jetzt nicht vor meinem Computer, um die Formel zu testen. Wenn es jedoch zB ein Zeichen zu wenig zurückgibt, fügen Sie dem Längenargument einfach eine +1 hinzu. Sobald Sie den Tag, den Monat und das Jahr (und gegebenenfalls die Uhrzeit) isoliert haben, können Sie mit der Datumsfunktion ein Datum rekonstruieren.
Ok, ich denke, ich könnte eine richtige Lösung für Sie haben. Es scheint, dass die Art und Weise, wie Excel Text in Datum konvertiert, von Ihren Windows-Regionseinstellungen abhängt. Durch Festlegen der Datumsformatierung der Zelle in Excel wird nur die Anzeige von Datumswerten geändert , nicht jedoch die Konvertierung.
Meine Regionseinstellungen in Windows sind derzeit auf Südafrika eingestellt. Meine Tabelle löst sich mit der nebenstehenden Formel = A1 + 0 auf:
Nun, wenn ich auf Windows Kopf Bedienfeld > Ändern von Datum, Uhrzeit oder Zahlenformaten und vorübergehend das Format in Englisch (USA) ändern, dann in die Tabelle zurückgehen und neu zu bewerten die Formeln (drücken Sie Strg + Alt + F9 , wenn es wird nicht automatisch aktualisiert):
Kopieren Sie nun die neue Datumswertspalte und fügen Sie sie als Werte über sich selbst ein. Speicher die Datei. Ändern Sie Ihre Regionseinstellungen wieder so, wie sie waren. Wählen Sie die Spalte aus und legen Sie das gewünschte Format für Datum und Uhrzeit unter "Benutzerdefiniert" ab:
Damit:
