Fórmula genérica
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Resumen
Para convertir texto en un formato de fecha no reconocido a una fecha adecuada de Excel, puede analizar el texto y ensamblar una fecha adecuada con una fórmula basada en varias funciones: FECHA, IZQUIERDA, MEDIA y DERECHA. En el ejemplo que se muestra, la fórmula en C6 es:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Esta fórmula extrae los valores de año, mes y día por separado y utiliza la función FECHA para ensamblarlos en la fecha 24 de octubre de 2000.
Antecedentes
Cuando trabaja con datos de otro sistema, puede encontrarse con una situación en la que las fechas no son reconocidas correctamente por Excel, que en su lugar trata las fechas como texto. Por ejemplo, puede tener valores de texto como este:
| Texto | Fecha representada |
| 20001024 | 24 de octubre de 2000 |
| 20050701 | 1 de julio de 20115 |
| 19980424 | 24 de abril de 1998 |
| 28.02.2014 | 28 de febrero de 2014 |
Cuando Excel ha evaluado un valor de fecha como texto, una opción es usar una fórmula para analizar el texto en sus componentes (año, mes, día) y usarlos para hacer una fecha con la función FECHA. Como se señaló anteriormente, le recomiendo que primero pruebe las soluciones a continuación (agregando cero y usando texto en las columnas) antes de usar una fórmula. Ambas soluciones son más rápidas y requieren menos esfuerzo.
Explicación
La función FECHA crea una fecha válida utilizando tres argumentos: año, mes y día:
=DATE(year,month,day)
En la celda C6, usamos las funciones IZQUIERDA, MEDIA y DERECHA para extraer cada uno de estos componentes de una cadena de texto e introducir los resultados en la función FECHA:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
La función IZQUIERDA extrae los 4 caracteres más a la izquierda para el año, la función MID extrae los caracteres en las posiciones 5-6 para el mes y la función DERECHA extrae los 2 caracteres más a la derecha como día. Cada resultado se devuelve directamente a la función FECHA. El resultado final es una fecha de Excel adecuada que puede formatearse como desee.
Este enfoque se puede personalizar según sea necesario. Por ejemplo, el formato de fecha no reconocido en la fila 8 es dd.mm.yyyy y la fórmula en C8 es:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Texto de formato largo
A veces puede tener fechas en un formato más largo como "11 de abril de 2020 08:43:13" que Excel no reconoce correctamente. En este caso, es posible que pueda ajustar la cadena de una manera que le permita a Excel reconocer correctamente la fecha con la función SUSTITUIR. La siguiente fórmula reemplaza la segunda instancia de un espacio ("") con una coma y un espacio (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Una vez que agreguemos la coma después del nombre del mes, Excel entenderá la fecha, pero aún necesita un pequeño "impulso". Es por eso que agregamos cero al final. La operación matemática hace que Excel intente convertir la cadena en un número. Cuando tiene éxito, esto dará como resultado una fecha válida de Excel. Tenga en cuenta que es posible que deba aplicar el formato de número de fecha para mostrar la fecha correctamente.
Sin fórmulas
Antes de usar una fórmula para analizar y construir manualmente una fecha a partir del texto, pruebe una de las siguientes soluciones. La primera opción usa una operación matemática para "empujar" un poco a Excel y forzarlo a intentar evaluar el texto como un número. Debido a que las fechas de Excel son de hecho números, esto a menudo puede funcionar. Es posible que deba aplicar un formato de fecha si las operaciones se realizan correctamente.
Agrega cero para fijar fechas
A veces, encontrará fechas en un formato de texto que Excel debería reconocer. En este caso, es posible que pueda obligar a Excel a convertir los valores de texto en fechas agregando cero al valor. Cuando agrega cero, Excel intentará convertir los valores de texto en números. Dado que las fechas son solo números, este truco es una excelente manera de convertir fechas en formato de texto que Excel realmente debería entender.
Para convertir las fechas en su lugar agregando cero, pruebe Pegado especial:
- Ingrese cero (0) en una celda no utilizada y copie al portapapeles
- Seleccione las fechas problemáticas
- Pegado especial> Valores> Agregar
- Aplicar un formato de fecha (si es necesario)
También puede agregar cero en una fórmula como esta:
=A1+0
donde A1 contiene una fecha no reconocida.
Texto a columnas para fijar fechas
Otra forma de hacer que Excel reconozca las fechas es usar la función Texto a columnas:
Seleccione la columna de fechas, luego intente Datos> Texto a columnas> Fijo> Finalizar
Si Excel reconoce las fechas, las arreglará todas en un solo paso.
