Ich kann Excel nicht dazu bringen, das Datum in Spalte

Das Problem: Excel will Datumsangaben nicht als Datumsangaben erkennen, obwohl Sie ihm über “Zellen formatieren - Anzahl - Benutzerdefiniert” explizit mitteilen wollen, dass es sich um Datumsangaben mit “mm/dd/yyyy” handelt. Wie Sie wissen, speichert Excel, wenn es etwas als Datum erkannt hat, dieses weiter als Zahl - wie “41004”, zeigt es aber als Datum gemäß dem von Ihnen angegebenen Format an. Um Verwirrung zu stiften, konvertiert Excel möglicherweise nur einen Teil Ihrer Daten, wie z.B. den 08.04.2009, lässt aber andere, wie z.B. den 28.07.2009, unkonvertiert.

Lösung: Schritte 1 und dann 2

1) Wählen Sie die Datumsspalte aus. Wählen Sie unter Daten die Schaltfläche Text zu Spalten. Lassen Sie auf dem ersten Bild den Auswahlknopf auf “ getrennt” stehen und klicken Sie auf Next. Deaktivieren Sie eines der Begrenzungskästchen (alle Kästchen blank ; keine Häkchen) und klicken Sie auf Next. Wählen Sie unter Spaltendatenformat Datum und wählen Sie MDY in der nebenstehenden Combo-Box und klicken Sie Finish. Jetzt haben Sie Datumswerte (d.h. Excel hat Ihre Werte als Date-Datentyp erkannt), aber die Formatierung ist wahrscheinlich immer noch das Datum im Gebietsschema und nicht das gewünschte mm/dd/yyyy.

2) Um das gewünschte US-Datumsformat richtig angezeigt zu bekommen, müssen Sie zuerst die Spalte auswählen (falls nicht ausgewählt) und dann unter Cell Format - Number Date UND Locale auswählen: Englisch (US). Dadurch erhalten Sie ein Format wie “m/d/yy”. Dann können Sie Benutzerdefiniert auswählen und dort entweder “mm/dd/yyyy” eingeben oder aus der Liste der benutzerdefinierten Zeichenfolgen wählen.

Alternativ : LibreOffice Calc verwenden. Beim Einfügen von Daten aus Patricks Beitrag wählen Sie “Paste Special” (Ctrl+Shift+V) und dann “Unformatierter Text”. Dadurch wird das Dialogfeld “Text importieren” geöffnet. Der Zeichensatz bleibt Unicode, aber für die Sprache wählen Sie English(USA); Sie sollten auch das Kästchen “Detect special numbers” ankreuzen. Ihre Daten erscheinen sofort im US-Standardformat und sind datums-sortierbar. Wenn Sie das spezielle US-Format MM/DD/YYYYY wünschen, müssen Sie dies einmal über “Format Cells” angeben - entweder vor oder nach dem Einfügen.

Man könnte sagen - Excel hätte Datumsangaben erkennen sollen, sobald ich es über “Cell Format” gesagt hätte, und ich könnte nicht mehr zustimmen. Leider konnte ich erst durch Schritt 1 von oben erreichen, dass Excel diese Textstrings als Datumsangaben erkennt. Wenn Sie das oft tun, ist es natürlich nervtötend, und Sie könnten eine visuelle Grundroutine zusammenstellen, die dies auf Knopfdruck für Sie erledigt.

Daten | Text in Spalten

Aktualisierung auf führendes Hochkomma nach dem Einfügen: Sie können in der Formelzeile sehen, dass in der Zelle, in der das Datum nicht erkannt wird, ein führendes Hochkomma steht. Das bedeutet, dass in der Zelle, die als Zahl (oder Datum) formatiert ist, ein Textstring vorhanden ist. Man könnte sagen - das führende Hochkomma verhindert, dass das Rechenblatt die Zahl erkennt. Sie müssen wissen, dass Sie in der Formelleiste danach suchen müssen - denn das Rechenblatt zeigt einfach etwas an, das wie eine linksbündige Zahl aussieht. Um dieses Problem zu beheben, wählen Sie die Spalte, die Sie korrigieren möchten, wählen Sie im Menü Data | Text to Columns und klicken Sie auf OK. Manchmal können Sie den Datentyp angeben, aber wenn Sie zuvor das Format der Spalte auf Ihren speziellen Datentyp eingestellt haben, benötigen Sie ihn nicht. Der Befehl ist eigentlich dazu gedacht, eine Textspalte mit Hilfe eines Trennzeichens in zwei oder mehr zu teilen, aber auch für dieses Problem funktioniert er wie ein Zauberspruch. Ich habe es in Libreoffice getestet, aber es gibt den gleichen Menüpunkt auch in Excel.

Wählen Sie alle Spalten aus und gehen Sie zu Suchen und Ersetzen und ersetzen Sie einfach "/" durch /.

Dies mag für den ursprünglichen Fragesteller nicht relevant sein, aber es könnte jemandem helfen, der nicht in der Lage ist, eine Spalte mit Datumsangaben zu sortieren.

Ich fand heraus, dass Excel eine Spalte mit Datumsangaben, die alle vor 1900 liegen, nicht erkennt, da es darauf besteht, dass es sich um eine Textspalte handelt (da der 1.1.1900 das numerische Äquivalent 1 hat und negative Zahlen anscheinend nicht erlaubt sind). Also ersetzte ich generell alle meine Daten (die in den 1800er Jahren lagen), um sie in die 1900er Jahre einzuordnen, z.B. 180 -> 190, 181 -> 191, usw. Der Sortiervorgang funktionierte dann gut. Schließlich ersetzte ich sie auf die andere Weise, z.B. 190 -> 180.

Hoffentlich hilft das einem anderen Historiker da draußen.

Ich befasste mich mit einem ähnlichen Problem mit Tausenden von Zeilen, die aus einer SAP-Datenbank extrahiert wurden, und, unerklärlicherweise, zwei verschiedenen Datumsformaten in derselben Datumsspalte (die meisten sind unser Standard “JJJJ-MM-TT”, aber etwa 10% sind “MM-TT-JJJ”). Die manuelle Bearbeitung von 650 Zeilen einmal im Monat war keine Option.

Keine (null… 0… null) der obigen Optionen funktionierte. Ja, ich habe sie alle ausprobiert. Das Kopieren in eine Textdatei oder der explizite Export nach txt hatte immer noch irgendwie keinen Einfluss auf das Format (oder das Fehlen desselben) der 10% Datumsangaben, die einfach nur in ihrer Ecke saßen und sich weigerten, sich zu verhalten.

Die einzige Möglichkeit, das Problem zu beheben, bestand darin, eine leere Spalte rechts neben der Spalte mit dem fehlerhaften Datum einzufügen und die folgende, recht vereinfachende Formel zu verwenden:

(angenommen, Ihre fehlerhaften Daten befinden sich in Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

Ich konnte dann die Ergebnisse in meine neue, berechnete Spalte über die fehlerhaften Daten kopieren, indem ich “Werte” einfügte und erst danach meine berechnete Spalte löschen konnte.

Ich hatte genau dasselbe Problem mit Terminen in Excel. Das Format entsprach den Anforderungen für ein Datum in Excel, aber ohne die Bearbeitung jeder Zelle würde das Datum nicht erkannt werden, und wenn man es mit Tausenden von Zeilen zu tun hat, ist es nicht praktikabel, jede Zelle manuell zu bearbeiten. Die Lösung besteht darin, eine Suche und Ersetzung in der Datumsreihe durchzuführen, in der ich den Hypen durch einen Bindestrich ersetzt habe. Excel durchläuft die Spalte, in der “like” durch “like” ersetzt wird, aber der resultierende Faktor ist, dass es nun die Daten wieder übereinstimmt! FIXIERT!

Es scheint, dass Excel Ihre Datumsangaben nicht als Datumsangaben erkennt, sondern als Text, daher erhalten Sie die Sortieroptionen von A bis Z. Wenn Sie es richtig machen, sollten Sie etwas wie das folgende erhalten:

Daher ist es wichtig, sicherzustellen, dass Excel das Datum erkennt. Der einfachste Weg, dies zu erreichen, ist die Tastenkombination STRG+UMSCHALT+3.

Hier ist, was ich mit Ihren Daten gemacht habe. Ich habe sie einfach aus Ihrem obigen Beitrag kopiert und in Excel eingefügt. Dann wandte ich die obige Abkürzung an und erhielt die erforderliche Sortieroption. Überprüfen Sie das Bild.

Ich würde vermuten, dass das Problem darin besteht, dass Excel das Format nicht versteht… Obwohl Sie das Datumsformat wählen, bleibt es als Text erhalten.

Sie können dies leicht testen: Wenn Sie versuchen, das Datumsformat zu aktualisieren, markieren Sie die Spalte, klicken Sie mit der rechten Maustaste darauf und wählen Sie Formatzellen (wie Sie es bereits tun), aber wählen Sie die Option 2001-03-14 (am unteren Ende der Liste). Dann überprüfen Sie das Format Ihrer Zellen.

Ich werde vermuten, dass nur einige der Datumsformate korrekt aktualisiert werden, was darauf hindeutet, dass Excel es immer noch wie eine Zeichenfolge und nicht wie ein Datum behandelt (beachten Sie die verschiedenen Formate im unteren Bildschirmfoto)!

Es gibt Umgehungsmöglichkeiten dafür, aber keine davon wird automatisiert, einfach weil Sie jedes Mal exportieren. Ich würde vorschlagen, die VBa zu verwenden, wo Sie einfach ein Makro ausführen können, das vom US-amerikanischen in das britische Datumsformat konvertiert, aber das würde bedeuten, dass Sie die VBa jedes Mal kopieren und in Ihr Blatt einfügen müssten.

Alternativ dazu erstellen Sie ein Excel, das die neu erstellten exportierten Excel-Blätter (aus dem Austausch) liest, und führen dann die VBa aus, um das Datumsformat für Sie zu aktualisieren. Ich gehe davon aus, dass die exportierte Exchange-Excel-Datei immer den gleichen Dateinamen/Verzeichnis hat und gebe Ihnen ein funktionierendes Beispiel:

Erstellen Sie also ein neues Excel-Blatt mit dem Namen ImportedData.xlsm (Excel aktiviert). Dies ist die Datei, in die wir die exportierte Exchange-Excel-Datei importieren werden!

Fügen Sie diese VBa zur Datei ImportedData.xlsm hinzu

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop

End Sub

Was ich auch getan habe, war, das Datumsformat auf jjjj-mm-tt zu aktualisieren, denn auf diese Weise funktioniert es immer noch, selbst wenn Excel Ihnen den Sortierfilter A-Z anstelle der neuesten Werte gibt!

Ich bin mir sicher, dass der obige Code Fehler enthält, aber ich habe natürlich keine Ahnung, welche Art von Daten Sie haben, aber ich habe ihn mit einer einzigen Spalte mit Datumswerten getestet (wie Sie es getan haben) und er funktioniert bei mir! Wie füge ich VBA in MS Office hinzu?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Einfache Lösung hier - erstellen Sie eine neue Spalte mit der Formel =Datenwert(zelle) und kopieren Sie die Formel dann in Ihre anderen Zeilen - einige Sekunden zur Korrektur

Ich habe es herausgefunden!

Am Anfang und am Ende des Datums ist ein Leerzeichen.

1. Wenn Sie Suchen und Ersetzen verwenden und die Leertaste drücken, wird es NICHT funktionieren.
2. Sie müssen rechts vor der Monatsnummer klicken, die Umschalttaste und den Pfeil nach links drücken, um auszuwählen und zu kopieren. Manchmal müssen Sie den Raum mit der Maus auswählen.
3. Dann fügen Sie dies als Leerzeichen in Suchen und Ersetzen ein, und alle Ihre Daten werden zu Daten.
4. Wenn Leerzeichen und Datum vorhanden sind, wählen Sie Daten>Zu Daten gehen>Zu Spalten gehen>Begrenzt>Leerzeichen als Trennzeichen und schließen Sie dann ab.
5. Alle Leerzeichen werden entfernt.

Ich hatte kein Glück mit all den oben genannten Vorschlägen - mir ist eine sehr einfache Lösung eingefallen, die wie ein Zauber wirkt. Fügen Sie die Daten in Google Sheets ein, markieren Sie die Datumsspalte, klicken Sie auf Format, dann auf Nummerierung und nummerieren Sie noch einmal, um sie in das Zahlenformat zu ändern. Dann kopiere ich die Daten und füge sie in die Excel-Tabelle ein, klicke auf die Datumsspalte, klicke auf Format, dann nummeriere und formatiere die Zellen bis Datum. Sehr schnell. Hoffentlich hilft das.

Ich kopierte einfach die Zahl 1 (eins) und multiplizierte sie mit der Funktion PASTE SPECIAL in die Datumsspalte (Daten). Es ändert sich dann in Allgemeine Formatierung d.h. 42102 Dann gehen Sie Datum auf die Formatierung anwenden und es erkennt es jetzt als Datum.

Hoffentlich hilft das

Meine Lösung im Vereinigten Königreich - ich hatte meine Daten mit Punkten wie folgt:

03.01.17

Ich löste dies mit folgendem:

1. Gehen Sie zum Suchen und Auswählen/Ersetzen.
2. Ich habe alle Punkte durch einen mittleren Strich ersetzt, z.B. 03.01.17 03-01-17.
3. Lassen Sie die Spalte hervorgehoben.
4. formatieren Sie die Zellen, wählen Sie Datum aus.
5. verwenden Sie den Typ 14-03-12 (für meinen unbedingt den mittleren Bindestrich)
6. locale English (United Kingdom)

Beim Sortieren der Spalte werden alle Daten für das Jahr sortiert.

EINE KLEINE LÄNGERE, ABER VIEL EINFACHERE LÖSUNG FÜR …… Keine Notwendigkeit, Makros oder geekiges Zeugs auszuführen…..einfache ms excel-Formeln und Editieren.

gemischte Daten excel-Schnappschuss:

• Das Datum ist im gemischten Format.
  
• Um ein symmetrisches Datumsformat zu erhalten, wurden daher zusätzliche Spalten geschaffen, indem die Datumsspalte im ‘gemischten Format’ in TEXT umgewandelt wurde.
• Aus diesem Text haben wir die Positionen von “/” identifiziert, und der mittlere Text wurde extrahiert, indem Datum, Monat, Jahr mit Hilfe der MID-Formel bestimmt wurden.
• Diejenigen, die ursprünglich Datumsangaben waren, endete die Formel mit dem Ergebnis FEHLER / #VALUE. -
• #VALUE wurden ausgewählt, wie sie sind, indem IFERROR zur Datumsformel hinzugefügt wurde, und die Spalte wurde wie gewünscht formatiert (hier tt/mmm/jj)

* VERWEIS AUF DEN SCHNAPFEN DES EXCEL-BLATTES ÜBER (= gemischter Daten-Excel-Schnappschuss) *

Ich habe die verschiedenen Vorschläge ausprobiert, fand aber die einfachste Lösung für mich wie folgt…

Siehe Bilder 1 und 2 für das Beispiel… (Anmerkung - einige Felder wurden absichtlich ausgeblendet, da sie nicht zum Beispiel beitragen). Ich hoffe, das hilft…

1. Feld C - ein Mischformat, das mich absolut verrückt gemacht hat. Auf diese Weise kamen die Daten direkt aus der Datenbank und hatten keine zusätzlichen Leerzeichen oder verrückten Zeichen. Bei der Anzeige als Text z.B. wurde der 01.01.2016 als Wert vom Typ ‘42504’ angezeigt, vermischt mit dem 15.04.2006, der so angezeigt wurde, wie er ist.

2. Feld F - wo ich die Länge von Feld C erhielt (die LEN-Formel wäre eine Länge von 5 von 10, je nach Datumsformat). Das Feld ist der Einfachheit halber im allgemeinen Format.

3. Feld G - Erhalt der Monatskomponente des gemischten Datums - auf der Grundlage des Längenergebnisses in Feld F (5 oder 10) erhalte ich entweder den Monat aus dem Datumswert in Feld C oder die Monatszeichen in der Zeichenfolge.

4. Feld H - Ermittlung der Tageskomponente des gemischten Datums - auf der Grundlage des Längenergebnisses in Feld F (5 oder 10) ermittle ich entweder den Tag aus dem Datumswert in Feld C oder ermittle die Tageszeichen in der Zeichenfolge.

Ich kann dies auch für das Jahr tun und dann ein Datum aus den drei Komponenten erzeugen, das konsistent ist. Andernfalls kann ich die einzelnen Tages-, Monats- und Jahreswerte in meiner Analyse verwenden. Bild 1: Basistabelle mit Werten und Feldnamen Bild 2: Tabelle mit Formeln, die der obigen Erklärung entsprechen

Ich hoffe, dies hilft.

Dieses Problem machte mich verrückt, dann stolperte ich über eine einfache Lösung. Zumindest funktionierte es für meine Daten. Es ist leicht zu machen und zu merken. Aber ich weiß nicht, warum es funktioniert, aber das Ändern von Typen, wie oben beschrieben, funktioniert nicht. 1. Wählen Sie die Spalte(n) mit den Daten 2. Suchen Sie nach einem Jahr in Ihren Daten, zum Beispiel 2015. Wählen Sie Alle durch dasselbe Jahr ersetzen, z.B. 2015. 3. Wiederholen Sie die Suche für jedes Jahr. Dies ändert die Typen in tatsächliche Daten, Jahr für Jahr. Dies ist immer noch umständlich, wenn Sie viele Jahre in Ihren Daten haben. Schneller ist es, 201 zu suchen und durch 201 zu ersetzen (um 2010 bis 2019 zu ersetzen); 200 durch 200 zu ersetzen (um 2000 bis 2009 zu ersetzen); und so weiter.

Wählen Sie Daten aus und führen Sie diesen Code darüber…

On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub

Alle oben genannten Lösungen - einschließlich r0berts - waren erfolglos, fanden aber diese bizarre Lösung, die sich als die schnellste Lösung herausstellte.

Ich versuchte, “Daten” auf einer heruntergeladenen Tabelle von Kontotransaktionen zu sortieren.

Diese war über den CHROME-Browser heruntergeladen worden. Keine Manipulation der “Daten” würde dazu führen, dass sie als alt-neu sortierbar erkannt würden.

Aber dann lud ich über den INTERNET EXPLORER-Browser herunter - erstaunlich - ich konnte sofort sortieren, ohne eine Spalte zu berühren.

Ich kann nicht erklären, warum verschiedene Browser die Formatierung der Daten beeinflussen, außer dass dies eindeutig der Fall war und eine sehr schnelle Lösung war.

Wenn Excel sich hartnäckig weigert, Ihre Spalte als Datum zu erkennen, ersetzen Sie sie durch eine andere :

• Fügen Sie rechts neben der alten Spalte eine neue Spalte hinzu
• Klicken Sie mit der rechten Maustaste auf die neue Spalte und wählen Sie Format
• Legen Sie das Format auf date fest - Markieren Sie die gesamte alte Spalte und kopieren Sie sie
• Markieren Sie die oberste Zelle der neuen Spalte und wählen Sie Paste Special, und fügen Sie nur values ein - Sie können nun die alte Spalte entfernen.

Dies geschieht ständig beim Austausch von Datumsdaten zwischen verschiedenen Gebietsschemata in Excel. Wenn Sie die Kontrolle über das VBA-Programm haben, das die Daten exportiert, schlage ich vor, statt des Datums selbst die Zahl zu exportieren, die das Datum darstellt. Die Zahl korreliert eindeutig mit einem einzelnen Datum, unabhängig von Formatierung und Gebietsschema. Zum Beispiel wird statt der CSV-Datei, die den 24.09.2010 anzeigt, 40445 angezeigt.

In der Exportschleife, wenn Sie jede Zelle halten, wenn es sich um ein Datum handelt, konvertieren Sie mit CLng(myDateValue) in eine Zahl. Dies ist ein Beispiel für meine Schleife, die durch alle Zeilen einer Tabelle läuft und in CSV exportiert wird (beachten Sie, dass ich auch Kommas in Zeichenketten durch ein Tag ersetze, das ich beim Importieren entferne, aber das brauchen Sie vielleicht nicht):

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

Ich benutze einen Mac.

Gehe zu Excel-Einstellungen> Bearbeiten> Datumsoptionen> Datumssystem automatisch konvertieren

Gehe auch zu Tabellen & Filter> Datumsgruppen beim Filtern.

Das Problem begann wahrscheinlich, als Sie eine Datei mit einem anderen Datumssystem erhielten, und das hat Ihre Excel-Datumsfunktion versaut

Normalerweise erstelle ich einfach eine zusätzliche Spalte für Sortier- oder Summierungszwecke, also verwenden Sie year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

Das liefert ein yyyymmdd-Ergebnis, das sortiert werden kann. Wenn Sie len(a1) verwenden, können Sie einfach eine zusätzliche Null für die Monate 1-9 hinzufügen.