Formula generica
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Sommario
Per convertire il testo in un formato di data non riconosciuto in una data Excel corretta, puoi analizzare il testo e assemblare una data corretta con una formula basata su diverse funzioni: DATA, SINISTRA, MEDIA e DESTRA. Nell'esempio mostrato, la formula in C6 è:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Questa formula estrae separatamente i valori di anno, mese e giorno e utilizza la funzione DATA per assemblarli nella data 24 ottobre 2000.
sfondo
Quando lavori con i dati di un altro sistema, potresti imbatterti in una situazione in cui le date non vengono riconosciute correttamente da Excel, che invece tratta le date come testo. Ad esempio, potresti avere valori di testo come questo:
| Testo | Data rappresentata |
| 20001024 | 24 ottobre 2000 |
| 20050701 | 1 luglio 20115 |
| 19980424 | 24 aprile 1998 |
| 28.02.2014 | 28 febbraio 2014 |
Quando Excel ha valutato un valore di data come testo, un'opzione è utilizzare una formula per analizzare il testo nei suoi componenti (anno, mese, giorno) e utilizzarli per creare una data con la funzione DATA. Come notato sopra, ti consiglio di provare prima le soluzioni seguenti (aggiungendo zero e usando il testo nelle colonne) prima di usare una formula. Entrambe le soluzioni sono più veloci e richiedono uno sforzo minore.
Spiegazione
La funzione DATE crea una data valida utilizzando tre argomenti: anno, mese e giorno:
=DATE(year,month,day)
Nella cella C6, utilizziamo le funzioni LEFT, MID e RIGHT per estrarre ciascuno di questi componenti da una stringa di testo e inserire i risultati nella funzione DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
La funzione LEFT estrae i 4 caratteri più a sinistra per l'anno, la funzione MID estrae i caratteri nelle posizioni 5-6 per il mese e la funzione RIGHT estrae i 2 caratteri più a destra come giorno. Ogni risultato viene restituito direttamente alla funzione DATE. Il risultato finale è una data Excel corretta che può essere formattata come preferisci.
Questo approccio può essere personalizzato in base alle esigenze. Ad esempio, il formato della data non riconosciuto nella riga 8 è gg.mm.aaaa e la formula in C8 è:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Testo lungo
A volte potresti avere date in una forma più lunga come "11 aprile 2020 08:43:13" che Excel non riconosce correttamente. In questo caso, potresti essere in grado di regolare la stringa in un modo che consenta a Excel di riconoscere correttamente la data con la funzione SOSTITUISCI. La formula seguente sostituisce la seconda istanza di uno spazio ("") con una virgola e uno spazio (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Una volta aggiunta la virgola dopo il nome del mese, Excel capirà la data, ma ha ancora bisogno di un piccolo "calcio". Ecco perché aggiungiamo zero alla fine. L'operazione matematica fa sì che Excel provi a convertire la stringa in un numero. In caso di successo, ciò risulterà in una data Excel valida. Tieni presente che potrebbe essere necessario applicare la formattazione del numero di data per visualizzare correttamente la data.
Senza formule
Prima di utilizzare una formula per analizzare e costruire manualmente una data dal testo, prova una delle soluzioni seguenti. La prima opzione utilizza un'operazione matematica per "spingere" un po 'Excel e costringerlo a provare a valutare il testo come un numero. Poiché le date di Excel sono in realtà numeri, questo spesso può fare il trucco. Potrebbe essere necessario applicare un formato data se le operazioni riescono.
Aggiungi zero per fissare le date
A volte, incontrerai le date in un formato di testo che Excel dovrebbe riconoscere. In questo caso, potresti essere in grado di forzare Excel a convertire i valori di testo in date aggiungendo zero al valore. Quando aggiungi zero, Excel proverà a convertire i valori di testo in numeri. Poiché le date sono solo numeri, questo trucco è un ottimo modo per convertire le date in un formato di testo che Excel dovrebbe davvero capire.
Per convertire le date in posizione aggiungendo zero, prova Incolla speciale:
- Immettere zero (0) in una cella inutilizzata e copiare negli appunti
- Seleziona le date problematiche
- Incolla speciale> Valori> Aggiungi
- Applicare un formato data (se necessario)
Puoi anche aggiungere zero in una formula come questa:
=A1+0
dove A1 contiene una data non riconosciuta.
Testo nelle colonne per correggere le date
Un altro modo per fare in modo che Excel riconosca le date è utilizzare la funzionalità Testo in colonne:
Seleziona la colonna delle date, quindi prova Dati> Testo su colonne> Fissa> Fine
Se Excel riconosce le date, le risolverà tutte in un unico passaggio.
