Hi ha moltes maneres de convertir les dates amb format de text en dates reals a Excel i aquest tutorial té com a objectiu explorar-les totes.
Quan importeu dades a Excel, poden tenir moltes formes diferents que Excel no reconeix. De vegades, quan les dates s'importen des d'una font externa, tindran format de text.
També és important saber que Excel aplica formats de data basats en la configuració de la regió del vostre sistema. Per tant, quan importeu dades a Excel que provenen d'un país diferent, és possible que Excel no les reconegui i les emmagatzemi com a entrades de text.
Si teniu aquest problema, hi ha moltes maneres de convertir el text en una data a Excel i les tractarem totes en aquest tutorial.
Mètodes per convertir text a data
Si el vostre full de treball conté dates amb format de text, en lloc de dates reals, no és possible utilitzar-les en cap càlcul. Per tant, cal tornar-los a convertir a dates reals.
Si veieu les dates alineades a l'esquerra, vol dir que tenen format de text perquè els textos estan alineats a l'esquerra de manera predeterminada. I el número i les dates sempre estan alineats a la dreta.
Hi ha poques maneres diferents de convertir text en una data a Excel, són:
- Opció de verificació d'errors
- Funció de text a columnes d'Excel
- Cerca i substitueix
- Eina especial enganxa
- Fórmula i funcions d'Excel
Converteix text a data mitjançant l'opció de comprovació d'errors
Excel té una funció de verificació d'errors integrada que detecta alguns errors evidents a les vostres dades. Si troba algun error, us mostrarà un petit triangle verd (un indicador d'error) a la cantonada superior esquerra de la cel·la, que té l'error. Si seleccioneu aquesta cel·la, apareixerà un signe d'avís amb un signe d'exclamació groc. Quan moveu el cursor sobre aquest signe, Excel us informarà sobre el possible problema amb aquesta cel·la.
Per exemple, quan introduïu l'any en format de dos dígits a la vostra data, Excel assumeix aquesta data com a text i l'emmagatzema com a text. I si seleccioneu aquesta cel·la, apareixerà un signe d'exclamació amb un avís: "Aquesta cel·la conté una cadena de data representada només amb 2 dígits de l'any".
Si les vostres cel·les mostren aquest indicador d'error, feu clic al signe d'exclamació i es mostraran algunes opcions per convertir les dates amb format de text en dates reals. Excel us mostrarà opcions per convertir-lo a 19XX o 20XX (19XX per a 1915, 20XX per a 2015). Seleccioneu l'opció adequada.
Aleshores, el text es convertirà al format de data adequat.
Com habilitar l'opció de comprovació d'errors a Excel
Normalment, l'opció Comprovació d'errors està activada a Excel de manera predeterminada. Si la funció de verificació d'errors no us funciona, heu d'activar la verificació d'errors a Excel.
Per fer-ho, feu clic a la pestanya "Fitxer" i seleccioneu "Opcions" al tauler esquerre.
A la finestra Opcions d'Excel, feu clic a "Fórmules" al tauler esquerre i, al tauler dret, activeu "Activa la comprovació d'errors de fons" a la secció Comprovació d'errors. I marqueu les "Cèl·lules que contenen anys representats com a 2 dígits" a la secció Regles de comprovació d'errors.
Converteix text a data utilitzant la funció de text a columna d'Excel
Text a columna és una característica excel·lent d'Excel que us permet dividir les dades en diverses columnes i també és una eina potent per convertir valors de text en valors de data. Aquest mètode reconeix diversos formats de dades diferents i els converteix al format de data adequat.
Conversió de cadenes de text simples a dates
Suposem que les vostres dates tenen el format de cadenes de text com aquesta:
Podeu utilitzar l'assistent de text a columnes per reformatar-les ràpidament totes a les dates.
Primer, seleccioneu l'interval d'entrades de text que voleu convertir en dates. A continuació, aneu a la pestanya "Dades" de la cinta i feu clic a l'opció "Text a columnes" al grup Eines de dades.
Apareixerà l'assistent de text a columnes. Al pas 1 de l'assistent de text a columna, trieu l'opció "Delimitat" a Tipus de dades original i feu clic a "Següent".
Al pas 2, desmarqueu totes les caselles "Delimitadors" i feu clic a Següent.
Al pas final de l'assistent, seleccioneu "Data" a Format de dades de columna i trieu el format de data a la llista desplegable següent "Dates" i feu clic al botó "Finalitzar". En el nostre cas, estem convertint les dates de text representades com "01 02 1995" (dia mes any), de manera que triem "DMY" a la llista desplegable "Data:".
Ara, Excel converteix les dates de text en dates reals i les mostra alineades a la dreta a les cel·les.
Nota: Abans de començar a utilitzar la funció de text a columna, assegureu-vos que totes les cadenes de text estiguin en un format idèntic, en cas contrari, els textos no es convertiran. Per exemple, si algunes de les dates de text tenen el format de mes/dia/any (MDY), mentre que altres són de dia/mes/any (DMY), i quan trieu "DMY" al pas 3, obtindreu resultats incorrectes. Tal com es mostra a la imatge següent.
Conversió de cadena de text complex a data
La funció de text a columnes és útil quan voleu convertir cadenes de text complexes en dates. Us permet utilitzar els delimitadors per identificar on s'han de dividir les vostres dades i mostrar-les en 2 o més columnes. I combina les parts dividides de les dates en la data sencera mitjançant la funció DATE.
Per exemple, si les dates es mostren en cadenes de text de diverses parts, com aquesta:
Dimecres, 01 de febrer de 2020
01 de febrer de 2020, 16.10 h
Podeu utilitzar l'assistent de text a columna per separar la informació del dia, la data i l'hora que està delimitada per una coma i mostrar-les en diverses columnes.
Primer, seleccioneu totes les cadenes de text que vulgueu convertir en dates. Feu clic al botó "Text a columnes" a la pestanya "Dades". Al pas 1 de l'assistent de text a columna, trieu l'opció "Delimitat" a Tipus de dades original i feu clic a "Següent".
Al pas 2 de l'assistent, seleccioneu els delimitadors que contenen les vostres cadenes de text i feu clic a "Següent". El nostre exemple de cadenes de text separades per comes i espais: "Dilluns, 1 de febrer de 2015, 13:00". Hauríem de triar "Coma" i "espais" com a delimitadors per dividir les cadenes de text en diverses columnes.
Al pas final, seleccioneu el format "General" per a totes les columnes a la secció Vista prèvia de dades. Especifiqueu on s'han d'inserir les columnes al camp "Destinació", si no ho feu, sobreescriurà les dades originals. Si voleu ignorar alguna part de les dades originals, feu-hi clic a la secció Previsualització de dades i seleccioneu l'opció "No importar columna (ometre)". A continuació, feu clic a "Finalitzar".
Ara, les parts de les dates (dies de la setmana, mes, any, hora) es divideixen en columnes B, C, D, E, F i G.
A continuació, combina les parts de la data amb l'ajuda d'una fórmula DATE per obtenir la data sencera.
La sintaxi de la funció DATA d'Excel:
=DATA(any,mes,dia)Al nostre exemple, les parts del mes, el dia i l'any es troben a les columnes C, D i E, respectivament.
La funció DATA només reconeix els números, no el text. Com que els nostres valors mensuals a la columna C són totes cadenes de text, hem de convertir-los en números. Per fer-ho, heu d'utilitzar la funció MES per canviar el nom del mes pel número del mes.
Per convertir el nom d'un mes en el número d'un mes, utilitzeu aquesta funció MES dins de la funció DATA:
=MES(1&C1)La funció MES afegeix 1 a la cel·la C2 que conté el nom del mes per convertir el nom del mes al número del mes corresponent.
Aquesta és la funció DATE que hem d'utilitzar per combinar parts de data de diferents columnes:
=DATA(E1,MES(1&C1),D1)
Ara utilitzeu el mànec d'ompliment a la cantonada inferior de la cel·la de la fórmula i apliqueu la fórmula a la columna.
Converteix text a data utilitzant el mètode de cerca i substitució
Aquest mètode utilitza delimitadors per canviar el format del text a les dates. Si el dia, el mes i l'any de les vostres dates estan separats per algun delimitador que no sigui un guió (-) o una barra inclinada (/), Excel no els reconeixerà com a dates i els emmagatzemarà com a text.
Per solucionar aquest problema, utilitzeu la funció Cerca i substitueix. Si canvieu els delimitadors de període no estàndard (.) amb barres inclinades (/) o guionet (-), Excel canviarà automàticament els valors per dates.
Primer, seleccioneu totes les dates de text que vulgueu convertir en dates. A la pestanya "Inici", feu clic al botó "Cerca i seleccioneu" a l'extrem dret de la cinta i seleccioneu "Substitueix". Alternativament, premeu Ctrl+H per obrir el quadre de diàleg Cerca i substitueix.
Al quadre de diàleg Cerca i substitueix, escriviu el delimitador que conté el vostre text (en el nostre cas punt (.) al camp "Cerca què" i una barra inclinada (/) o un guionet (-) al camp "Substitueix per" Feu clic al botó "Substitueix-ho tot" per substituir els delimitadors i feu clic a "Tanca" per tancar la finestra.
Ara, Excel reconeix que les vostres cadenes de text són ara dates i les forma automàticament com a dates. Les vostres dates s'alinearan a la dreta tal com es mostra a continuació.
Converteix text a data amb l'eina especial Enganxa
Una altra manera ràpida i senzilla de convertir cadenes de text en dates és afegint 0 a la cadena de text mitjançant l'opció especial d'enganxar. Afegir zero al valor converteix el text en el número de sèrie de la data que podeu formatar com a data.
Primer, seleccioneu una cel·la buida i copieu-la (seleccioneu-la i premeu Ctrl + C copiar).
A continuació, seleccioneu les cel·les que contenen les dates de text que voleu convertir, feu clic amb el botó dret i seleccioneu l'opció "Enganxa especial".
Al diàleg Enganxa especial, seleccioneu "Tot" a la secció Enganxa, trieu "Afegeix" a la secció Operació i feu clic a "D'acord".
També podeu realitzar altres operacions aritmètiques restar/multiplicar/dividir el valor de la cel·la de destinació amb el valor enganxat (com multiplicar cel·les amb 1 o dividir per 1 o restar zero).
Quan seleccioneu "Afegeix" a l'operació, afegeix "zero" a totes les dates de text seleccionades, ja que afegir un "0" no canvia els valors, obtindreu el número de sèrie de cada data. Ara tot el que heu de fer és canviar el format de les cel·les.
Seleccioneu els números de sèrie i, a la pestanya "Inici", feu clic a la llista desplegable "Format de número" al grup Número. Seleccioneu l'opció "Data curta" al menú desplegable.
Com podeu veure ara, els números estan formatats com a dates i alineats a la dreta.
Converteix text a data amb fórmules
Hi ha dues funcions que s'utilitzen principalment per convertir text a data: DATEVALUE i VALUE.
Utilitzant la funció DATEVALUE d'Excel
La funció DATEVALUE d'Excel és una de les maneres més senzilles de convertir una data representada com a text en el número de sèrie d'una data.
La sintaxi de la funció DATEVALUE:
=DATEVALUE(text_data)Argument: data_text especifica la cadena de text que voleu ocultar o fer referència a la cel·la que conté dates de text.
La fórmula:
=DATEVALUE(A1)La imatge següent il·lustra com la funció DATEVALUE gestiona uns quants formats de data diferents que s'emmagatzemen com a text.
Teniu els números de sèrie de la data, ara heu d'aplicar el format de la data a aquests números. Per fer-ho, seleccioneu les cel·les amb números de sèrie, després aneu a la pestanya "Inici" i trieu "Data curta" a la llista desplegable "Format de número".
Ara teniu les dates amb format a la columna C.
Encara que no hi hagi cap part de l'any a la data de text (A8), DATEVALUE utilitzarà l'any actual del rellotge de l'ordinador.
La funció DATEVALUE només convertirà els valors de text que semblen una data. No pot convertir un text que s'assembla a un número fins ara, ni pot canviar un valor de nombre fins a la data, per això necessitareu la funció VALOR d'Excel.
Utilitzant la funció VALOR d'Excel
La funció VALOR d'Excel és capaç de convertir qualsevol cadena de text que s'assembli a una data o un nombre en un valor numèric, de manera que és molt útil a l'hora de convertir qualsevol nombre, no només dates.
La funció VALOR:
=VALOR(text)text– la cadena de text que volem convertir o fer referència a la cel·la que conté la cadena de text.
La fórmula d'exemple per convertir la data del text:
=VALOR(A1)La fórmula VALUE a continuació pot canviar qualsevol cadena de text que sembli una data en un número, tal com es mostra a continuació.
Tanmateix, la funció VALUE no admet tots els tipus de valors de data. Per exemple, si les dates utilitzen decimals (A11), retornarà el #VALOR! error.
Un cop tingueu el número de sèrie de la vostra data, haureu de formatar la cel·la amb el número de sèrie de la data perquè sembli una data com hem fet per a la funció DATEVALUE. Per fer-ho, seleccioneu els números de sèrie i seleccioneu l'opció "Data" al menú desplegable "Format de número" a la pestanya "Inici".
Això és tot, aquestes són les 5 maneres diferents de convertir dates amb format de text a dates a Excel.