Impossibile far riconoscere la data in Excel nella colonna

Il problema: Excel non vuole riconoscere le date come date, anche se attraverso “Format cells - Number - Custom” si sta esplicitamente cercando di dirgli che queste sono date da “mm/dd/yyyy”. Come sapete, quando Excel ha riconosciuto qualcosa come data, la memorizza ulteriormente come numero - come “41004” ma visualizza come data secondo il formato da voi specificato. Per aggiungere confusione, excel può convertire solo una parte delle vostre date, come ad esempio il 08/04/2009, ma lascia altri, ad esempio il 28/07/2009 non convertiti.

Soluzione: passi 1 e poi 2

1) Selezionare la colonna della data. Sotto Dati scegliere il pulsante Text to Columns. Nella prima schermata lasciare il pulsante di opzione su “ ** delimitato” e fare clic su Prossimo. Deselezionare una qualsiasi delle caselle del delimitatore (qualsiasi casella blank ; nessun segno di spunta) e cliccare Next. Sotto il formato dei dati della colonna scegliere Date e selezionare MDY nella casella combinata adiacente e fare clic su Finish. Ora avete i valori della data (cioè Excel ha riconosciuto i vostri valori come tipo di dati Date), ma la formattazione è probabilmente ancora la data locale, non il mm/dd/yyyy che volete.

2) Per ottenere il formato di data USA desiderato visualizzato correttamente dovete prima selezionare la colonna (se non selezionata) poi sotto Formato cella - Numero scegliete Data E selezionate Locale : Inglese (US). In questo modo si ottiene un formato come “m/d/yy”. Poi potete selezionare Custom e lì potete digitare “mm/dd/yyyy” o scegliere questo dalla lista delle stringhe personalizzate.

Alternative : usate LibreOffice Calc. Dopo aver incollato i dati dal post di Patrick scegliete Incolla speciale (Ctrl+Shift+V) e scegliete Testo non formattato. Si aprirà la finestra di dialogo “Importa testo”. Il set di caratteri rimane Unicode ma per la lingua scegliete English(USA); dovreste anche spuntare la casella “Rileva numeri speciali”. Le vostre date appaiono immediatamente nel formato USA predefinito e sono databili. Se si desidera il formato speciale USA MM/GG/AAAAA è necessario specificarlo una volta tramite “formato Cells” - prima o dopo l'incollatura.

Si potrebbe dire - Excel dovrebbe avere le date riconosciute non appena gli ho detto tramite “Cell Format” e Non potrei essere più d'accordo. Purtroppo è solo attraverso il passo 1 dall'alto che sono riuscito a far riconoscere ad Excel queste stringhe di testo come date. Ovviamente se lo fai spesso è un dolore al collo e potresti mettere insieme una routine visiva di base che lo farebbe per te con una semplice pressione di un pulsante.

Data | Testo alle colonne

Aggiorna su apostrofo principale dopo aver incollato: Puoi vedere nella barra delle formule che nella cella dove la data non è riconosciuta c'è un apostrofo principale. Ciò significa che nella cella formattata come numero (o data) c'è una stringa di testo. Si potrebbe dire - l'apostrofo iniziale impedisce al foglio di calcolo di riconoscere il numero. È necessario sapere di cercare nella barra delle formule per questo - perché il foglio di calcolo mostra semplicemente quello che sembra un numero allineato a sinistra. Per risolvere questo problema selezionate la colonna che volete correggere, scegliete nel menu Data | Text to Columns e cliccate su OK. A volte sarete in grado di specificare il tipo di dati, ma se avete precedentemente impostato il formato della colonna per il vostro particolare tipo di dati - non ne avrete bisogno. Il comando serve davvero a dividere una colonna di testo in due o più colonne utilizzando un delimitatore, ma funziona come un incantesimo anche per questo problema. L'ho testato in Libreoffice, ma c'è la stessa voce di menu anche in Excel.

Selezionare tutte le colonne e andare su Localizza e Sostituisci e sostituire "/" con /.

Questo può non essere rilevante per l'interrogante originale, ma può aiutare qualcun altro che non è in grado di ordinare una colonna di date.

Ho scoperto che Excel non riconoscerebbe una colonna di date che sono tutte anteriori al 1900, insistendo sul fatto che si tratta di una colonna di testo (dal 1/1/1900 ha l'equivalente numerico 1, e i numeri negativi non sono apparentemente ammessi). Così ho fatto una sostituzione generale di tutte le mie date (che erano nel 1800) per inserirle nel 1900, ad esempio 180 -> 190, 181 -> 191, ecc. Il processo di selezione ha poi funzionato bene. Alla fine ho fatto una sostituzione nell'altro modo, per esempio 190 -> 180.

Spero che questo aiuti qualche altro storico là fuori.

Mi stavo occupando di un problema simile con migliaia di righe estratte da un database SAP e, inspiegabilmente, due diversi formati di data nella stessa colonna di data (la maggior parte è il nostro standard “AAAA-MM-GG” ma circa il 10% è “MM-GG-AAAA”). La modifica manuale di 650 righe una volta al mese non era un'opzione.

Nessuna (zero… 0… zero) delle opzioni sopra elencate ha funzionato. Sì, le ho provate tutte. Copiare su un file di testo o esportare esplicitamente in txt ancora, in qualche modo, non ha avuto alcun effetto sul formato (o sulla mancanza di esso) delle date del 10% che si sono semplicemente seduti nel loro angolo rifiutandosi di comportarsi.

L'unico modo in cui sono riuscito a risolvere il problema è stato quello di inserire una colonna vuota a destra della colonna delle date di comportamento errato, utilizzando la seguente formula piuttosto semplicistica:

(supponendo che i dati di comportamento errato siano 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")))

Ho potuto poi copiare i risultati nella mia nuova colonna calcolata sopra le date di comportamento errato, incollando “Valori” solo dopo di che ho potuto cancellare la mia colonna calcolata.

Ho avuto lo stesso identico problema con le date in Excel. Il formato corrispondeva ai requisiti per una data in excel, ma senza modificare ogni cella non avrebbe riconosciuto la data, e quando si ha a che fare con migliaia di righe non è pratico modificare manualmente ogni cella. La soluzione è quella di eseguire una ricerca e sostituire sulla riga di date in cui ho sostituito l'ipen con un trattino. Excel corre attraverso la colonna sostituendo come con like ma il fattore risultante è che ora riconosca le date! FISSO!

Sembra che Excel non riconosca le vostre date come date, le riconosce come testo, quindi si ottiene l'opzione Ordina come dalla A alla Z. Se lo si fa correttamente, si dovrebbe ottenere qualcosa di simile a questo:

Quindi, è importante assicurarsi che Excel riconosca la data. Il modo più semplice per farlo è utilizzare la scorciatoia CTRL+SHIFT+3.

Ecco cosa ho fatto ai vostri dati. Li ho semplicemente copiati dal vostro post qui sopra e li ho incollati in Excel. Poi ho applicato la scorciatoia di cui sopra, e ho ottenuto l'opzione di ordinamento richiesto. Controlla l'immagine.

Sospetto che il problema sia che Excel non sia in grado di capire il formato… Anche se si seleziona il formato Data, rimane come Testo.

È possibile testare questo facilmente: Quando provate ad aggiornare il formato delle date, selezionate la colonna e cliccate con il tasto destro del mouse su di essa e scegliete le celle di formato (come già fate), ma scegliete l'opzione 2001-03-14 (in fondo alla lista). Poi esaminate il formato delle vostre celle.

Sospetto che solo alcuni dei formati di data si aggiornano correttamente, indicando che Excel lo tratta ancora come una stringa, non come una data (notate i diversi formati nella schermata sottostante)!

Ci sono dei metodi di lavoro per questo, ma, nessuno di essi sarà automatizzato semplicemente perché ogni volta state esportando. Suggerirei di utilizzare VBa, dove si può semplicemente eseguire una macro che converte il formato della data dagli Stati Uniti al Regno Unito, ma, questo significherebbe copiare e incollare la VBa nel vostro foglio ogni volta.

In alternativa, si crea un Excel che legge i fogli Excel appena creati esportati (da scambio) e poi si esegue la VBa per aggiornare il formato della data per voi. Suppongo che il file Excel di Exchange Exported Exchange avrà sempre lo stesso nome/cartella e fornirà un esempio funzionante:

Quindi, create un nuovo foglio Excel, chiamato ImportedData.xlsm (excel abilitato). Questo è il file in cui importeremo il file Exported Exchange Excel in!

Aggiungete questo VBa al file ImportedData.xlsm

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

Quello che ho fatto è stato anche aggiornare il formato della data a yyyy-mm-dd perché in questo modo, anche se Excel vi dà il filtro di ordinamento A-Z invece dei valori più recenti, funziona ancora!

Sono sicuro che il codice di cui sopra ha dei bug, ma ovviamente non ho idea di che tipo di dati avete, ma l'ho testato con una singola colonna di date (come avete fatto voi) e per me funziona bene! Come si aggiunge VBA in MS Office?

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

Semplice soluzione qui - creare una nuova colonna usare la formula =datevalue(cell) e poi copiare la formula nelle altre righe - pochi secondi per fissare

Ho capito!

C'è uno spazio all'inizio e alla fine della data.

1. Se si usa trova e si sostituisce e si preme la barra spaziatrice, NON funzionerà.
2. Se si usa la barra spaziatrice, NON funzionerà. 3. Per selezionare e copiare è necessario fare clic subito prima del numero del mese, premere shift e la freccia sinistra per selezionare e copiare. A volte è necessario utilizzare il mouse per selezionare lo spazio.
3. Poi incolla questo come lo spazio in Trova e sostituisci e tutte le tue date diventeranno date.
4. Se c'è spazio e data Seleziona Dati>Vai a Dati>Testo alle colonne>Delimita>Spazio come separatore e poi finisci.
5. Tutti gli spazi saranno rimossi.

Non ho avuto fortuna con tutti i suggerimenti di cui sopra - mi è venuta in mente una soluzione molto semplice che funziona come un incantesimo. Incolla i dati in Google Sheets, evidenzia la colonna della data, clicca sul formato, poi sul numero e ancora una volta sul numero per cambiarlo in formato numerico. Copio e incollo i dati nel foglio di calcolo Excel, clicco sulle date, poi formatto le celle fino ad oggi. Molto veloce. Spero che questo aiuti.

Ho semplicemente copiato il numero 1 (uno) e l'ho moltiplicato per la colonna della data (dati) utilizzando la funzione PASTE SPECIAL. Poi si passa alla formattazione generale i.e 42102 Poi si va ad applicare Data sulla formattazione e la si riconosce ora come data.

Spero che questo aiuti

La mia soluzione nel Regno Unito - Avevo i miei appuntamenti con dei punti come questo:

03.01.17

Ho risolto il problema con i seguenti:

1. Evidenziare l'intera colonna.
2. Evidenziare l'intera colonna.
3. Evidenziare l'intera colonna. 2. Andare a trovare e selezionare/sostituire.
4. 3. Ho sostituito tutti i punti fermi con il trattino centrale, ad esempio 03.01.17 03-01-17.
5. Ho sostituito tutti i punti fermi con il trattino centrale, ad esempio 03.01.17 03-01-17. Mantenere la colonna evidenziata.
6. Formattare le celle, scheda numero selezionare la data.
7. Utilizzare il tipo 14-03-12 (essenziale per il mio per avere il trattino centrale)
8. Locale English (Regno Unito)

Quando si ordina la colonna tutte le date per l'anno sono ordinate.

SHARING A LUNGA LUNGA LUNGA LUNGHEZZA MA MOLTA FACILE SOLUZIONE PER L'OGGETTO……. Non c'è bisogno di eseguire macro o roba geeky…….semplici formule ms excel e l'editing.

date miste excel snapshot:

• La data è nel formato misto.
  
• Quindi, per fare un formato di data simmetrico, sono state create delle colonne extra convertendo quella colonna di data “formato misto” in TESTO.
• Da quel testo abbiamo identificato le posizioni di “/” e il testo centrale è stato estratto determinando la data, il mese, l'anno con la formula MID.
• Quelle che in origine erano date, la formula si è conclusa con il risultato ERRORE / #VALUE. - Infine, dalla stringa che abbiamo creato - abbiamo convertito quelle che erano date con la formula DATA.
• #VALUE sono stati scelti così com'è da IFERROR aggiunto alla formula DATA & la colonna è stata formattata come richiesto (qui, gg/mmm/aa)

* RIFERIMENTO ALLA SNAPSHOT DI EXCEL SHEET ABOVE (= date miste excel snapshot) *

Ho provato i vari suggerimenti, ma la soluzione più semplice per me è stata la seguente…

Vedi le immagini 1 e 2 per l'esempio… (nota - alcuni campi sono stati nascosti intenzionalmente perché non contribuiscono all'esempio). Spero che questo aiuti…

1. Campo C - un formato misto che mi ha fatto impazzire. Ecco come i dati provenivano direttamente dal database e non avevano spazi extra o caratteri folli. Quando lo si visualizza come testo, ad esempio, il 01/01/2016 veniva visualizzato come valore di tipo ‘42504’, mescolato con il 04/15/2006 che mostrava così com'è.

2. Campo C - un formato misto che mi ha fatto impazzire… 2. Campo F - dove ho ottenuto la lunghezza del campo C (la formula LEN sarebbe una lunghezza di 5 di 10 a seconda del formato della data). Il campo è formato generale per semplicità.

3. Campo F - dove ho ottenuto la lunghezza del campo C (la formula della LEN sarebbe una lunghezza di 5 di 10 a seconda del formato della data). 3. Campo G - dove si ottiene la componente mese della data mista - in base al risultato della lunghezza nel campo F (5 o 10), ottengo il mese dal valore della data nel campo C, oppure ottengo i caratteri del mese nella stringa.

4. Campo F - dove si ottiene la lunghezza del campo C (la formula LEN sarebbe una lunghezza di 5 di 10 a seconda del formato della data). 4. Il campo H - che ottiene la componente giorno della data mista - in base al risultato della lunghezza nel campo F (5 o 10), ottengo il giorno dal valore della data nel campo C, o ottengo i caratteri del giorno nella stringa.

Posso fare questo anche per l'anno, poi creo una data dai tre componenti che è coerente. Altrimenti, posso usare i singoli valori del giorno, del mese e dell'anno nella mia analisi. Immagine 1: foglio di calcolo di base che mostra i valori e i nomi dei campi Immagine 2: foglio di calcolo che mostra le formule che corrispondono alla spiegazione sopra

Spero che questo aiuti.

Questo problema mi stava facendo impazzire, poi mi sono imbattuto in una soluzione facile. Almeno ha funzionato per i miei dati. È facile da fare e da ricordare. Ma non so perché funzioni, ma cambiare tipo come indicato sopra non lo fa. 1. 1. Selezionare la colonna o le colonne con le date 2. 3. Cerca un anno nelle tue date, per esempio il 2015. 3. Selezionare Sostituisci tutto con lo stesso anno, 2015. 3. 3. Ripetere per ogni anno. Questo cambia i tipi alle date effettive, anno per anno. Questo è ancora complicato se avete molti anni nei vostri dati. Più veloce è cercare e sostituire 201 con 201 (per sostituire dal 2010 al 2019); sostituire 200 con 200 (per sostituire dal 2000 al 2009); e così via.

Selezionare le date ed eseguire questo codice su di esso…

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

Tutte le soluzioni di cui sopra - incluso r0berts - non hanno avuto successo, ma hanno trovato questa bizzarra soluzione che si è rivelata la soluzione più veloce.

Stavo cercando di ordinare le “date” su un foglio di calcolo scaricato delle transazioni del conto.

Questo era stato scaricato tramite il browser CHROME. Nessuna quantità di manipolazione delle “date” sarebbe stata riconosciuta come vecchia o nuova ordinabile.

Ma poi ho scaricato tramite il browser INTERNET EXPLORER - incredibile - ho potuto ordinare istantaneamente senza toccare una colonna.

Non riesco a spiegare perché i diversi browser influenzano la formattazione dei dati, se non per il fatto che è stato chiaramente fatto ed è stata una soluzione molto veloce.

Se Excel si rifiuta ostinatamente di riconoscere la vostra colonna come data, sostituitela con un'altra :

• Aggiungete una nuova colonna a destra della vecchia colonna
• Cliccate con il tasto destro del mouse sulla nuova colonna e selezionate Format
• Impostate il formato su date
• Evidenziate l'intera vecchia colonna e copiatela
• Evidenziate la cella superiore della nuova colonna e selezionate Paste Special, e incollate solo values
• Ora potete rimuovere la vecchia colonna.

Questo succede sempre quando si scambiano dati di data tra i locali in Excel. Se avete il controllo sul programma VBA che esporta i dati, vi suggerisco di esportare il numero che rappresenta la data invece della data stessa. Il numero è correlato in modo univoco con una singola data, indipendentemente dalla formattazione e dal locale. Per esempio, invece del file CSV che mostra il 24/09/2010 mostrerà 40445.

Nel ciclo di esportazione quando si tiene in mano ogni cella, se si tratta di una data, convertire in numero usando CLng(myDateValue). Questo è un esempio del mio loop che attraversa tutte le righe di una tabella ed esporta in CSV (si noti che sostituisco anche le virgole nelle stringhe con un tag che tolgo durante l'importazione, ma questo potrebbe non essere necessario):

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

Uso un mac.

Vai alle preferenze excel> Modifica> Opzioni data> Converti automaticamente il sistema di data

Inoltre,

Vai a Tabelle e Filtri> Raggruppa le date quando filtra.

Il problema è probabilmente iniziato quando hai ricevuto un file con un sistema di data diverso, e questo ha rovinato la tua funzione di data excel

Di solito creo solo una colonna extra per lo smistamento o la totalizzazione, in modo da utilizzare year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

che fornirà un risultato yyyymmdd che può essere ordinato. L'uso di len(a1) permette solo di aggiungere uno zero extra per i mesi 1-9.