Hay muchas formas de convertir fechas formateadas como texto en fechas reales en Excel y este tutorial tiene como objetivo explorarlas todas.
Cuando importa datos a Excel, es posible que se presenten en muchas formas diferentes que Excel no reconoce. A veces, cuando las fechas se importan de una fuente externa, vendrán formateadas como texto.
También es importante saber que Excel aplica formatos de fecha según la configuración regional de su sistema. Por lo tanto, cuando importa datos a Excel que provienen de un país diferente, es posible que Excel no los reconozca y los almacene como entradas de texto.
Si tiene este problema, hay muchas formas que puede utilizar para convertir texto a una fecha en Excel y las cubriremos todas en este tutorial.
Métodos para convertir texto a fecha
Si su hoja de trabajo contiene fechas con formato de texto, en lugar de fechas reales, no es posible utilizarlas en ningún cálculo. Por lo tanto, debe convertirlos de nuevo a fechas reales.
Si ve sus fechas alineadas a la izquierda, significa que están formateadas como texto porque los textos están alineados a la izquierda de forma predeterminada. Y el número y las fechas siempre están alineados a la derecha.
Hay algunas formas diferentes de convertir texto a una fecha en Excel, son:
- Opción de comprobación de errores
- Función de texto a columnas de Excel
- Buscar y reemplazar
- Pegar herramienta especial
- Fórmula y funciones de Excel
Convertir texto a fecha usando la opción de verificación de errores
Excel tiene una función de verificación de errores incorporada que detecta algunos errores obvios en sus datos. Si encuentra algún error, le mostrará un pequeño triángulo verde (un indicador de error) en la esquina superior izquierda de la celda, que tiene el error. Si selecciona esa celda, aparecerá un signo de precaución con un signo de exclamación amarillo. Cuando mueva el cursor sobre ese signo, Excel le informará sobre el posible problema con esa celda.
Por ejemplo, cuando ingresa el año en el formato de dos dígitos en su fecha, Excel asume esa fecha como texto y la almacena como texto. Y si selecciona esa celda, aparecerá un signo de exclamación con una advertencia: "Esta celda contiene una cadena de fecha representada con solo 2 dígitos del año".
Si sus celdas muestran ese indicador de error, haga clic en el signo de exclamación y se mostrarán algunas opciones para convertir las fechas formateadas como texto en fechas reales. Excel le mostrará opciones para convertirlo a 19XX o 20XX (19XX para 1915, 20XX para 2015). Seleccione la opción adecuada.
Luego, el texto se convertirá al formato de fecha adecuado.
Cómo habilitar la opción de verificación de errores en Excel
Por lo general, la opción Comprobación de errores está activada en Excel de forma predeterminada. Si la función de verificación de errores no funciona para usted, debe habilitar la verificación de errores en Excel.
Para hacer esto, haga clic en la pestaña "Archivo" y seleccione "Opciones" en el panel de la izquierda.
En la ventana Opciones de Excel, haga clic en "Fórmulas" en el panel de la izquierda y, en el panel del lado derecho, habilite "Habilitar la verificación de errores en segundo plano" en la sección Verificación de errores. Y marque las "Celdas que contienen años representados como 2 dígitos" dentro de la sección Reglas de verificación de errores.
Convertir texto a fecha usando la función de texto a columna de Excel
Text to Column es una gran característica en Excel que le permite dividir datos en múltiples columnas, y también es una poderosa herramienta para convertir valores de texto a valores de fecha. Este método reconoce varios formatos de datos diferentes y los convierte al formato de fecha adecuado.
Conversión de cadenas de texto simples en fechas
Supongamos que sus fechas están formateadas en cadenas de texto como esta:
Puede usar el asistente de Texto a columnas para volver a formatearlos rápidamente a las fechas.
Primero, seleccione el rango de entradas de texto que desea convertir en fechas. Luego, vaya a la pestaña "Datos" en la cinta y haga clic en la opción "Texto a columnas" en el grupo Herramientas de datos.
Aparecerá el asistente Texto a columnas. En el paso 1 del asistente Texto a columna, elija la opción "Delimitado" en Tipo de datos original y haga clic en "Siguiente".
En el paso 2, desmarque todas las casillas "Delimitadores" y haga clic en Siguiente.
En el paso final del asistente, seleccione "Fecha" en Formato de datos de columna y elija su formato de fecha en la lista desplegable junto a "Fechas", y haga clic en el botón "Finalizar". En nuestro caso, estamos convirtiendo las fechas de texto representadas como "01 02 1995" (día mes año), por lo que elegimos "DMY" de la lista desplegable "Fecha:".
Ahora, Excel convierte las fechas de su texto en fechas reales y las muestra alineadas a la derecha en las celdas.
Nota: Antes de comenzar a usar la función Texto a columna, asegúrese de que todas las cadenas de texto tengan un formato idéntico; de lo contrario, los textos no se convertirán. Por ejemplo, si algunas de sus fechas de texto tienen el formato de mes / día / año (MDY) mientras que otras son de día / mes / año (DMY), y cuando elige "DMY" en el paso 3, obtendrá resultados incorrectos. Como se muestra en la imagen de abajo.
Conversión de una cadena de texto compleja a la fecha
La función Texto a columnas es útil cuando desea convertir cadenas de texto complejas en fechas. Le permite usar los delimitadores para identificar dónde se deben dividir y mostrar sus datos en 2 o más columnas. Y combine las partes divididas de las fechas en una fecha completa usando la función FECHA.
Por ejemplo, si sus fechas se muestran en cadenas de texto de varias partes, así:
Miércoles, 01 de febrero de 2020
01 de febrero de 2020 a las 4.10 p.m.
Puede utilizar el asistente Texto a columna para separar la información de día, fecha y hora delimitada por una coma y mostrarla en varias columnas.
Primero, seleccione todas las cadenas de texto que desee convertir en fechas. Haga clic en el botón "Texto en columnas" en la pestaña "Datos". En el paso 1 del asistente Texto a columna, elija la opción "Delimitado" en Tipo de datos original y haga clic en "Siguiente".
En el paso 2 del asistente, seleccione los delimitadores que contienen sus cadenas de texto y haga clic en "Siguiente". Las cadenas de texto de nuestro ejemplo separadas por coma y espacio - "Lunes, 01 de febrero de 2015, 1:00 PM". Debemos elegir "Comas" y "espacios" como delimitadores para dividir las cadenas de texto en varias columnas.
En el último paso, seleccione el formato "General" para todas las columnas en la sección Vista previa de datos. Especifique dónde deben insertarse las columnas en el campo "Destino"; si no lo hace, sobrescribirá los datos originales. Si desea ignorar alguna parte de los datos originales, haga clic en ella en la sección Vista previa de datos y seleccione la opción "No importar columna (omitir)". Luego, haga clic en "Finalizar".
Ahora, las partes de las fechas (días de la semana, mes, año, hora) se dividen en las columnas B, C, D, E, F y G.
Luego, combine las partes de la fecha con la ayuda de una fórmula FECHA para obtener la fecha completa.
La sintaxis de la función FECHA de Excel:
= FECHA (año, mes, día)En nuestro ejemplo, las partes de mes, día y año están en las columnas C, D y E, respectivamente.
La función FECHA solo reconoce números, no texto. Dado que nuestros valores de mes en la columna C son cadenas de texto, debemos convertirlos en números. Para hacer eso, necesita usar la función MES para cambiar el nombre del mes al número del mes.
Para convertir el nombre de un mes en el número de un mes, use esta función MES dentro de la función FECHA:
= MES (1 y C1)La función MES agrega 1 a la celda C2 que contiene el nombre del mes para convertir el nombre del mes al número de mes correspondiente.
Esta es la función FECHA que necesitamos usar para combinar partes de fecha de diferentes columnas:
= FECHA (E1, MES (1 y C1), D1)
Ahora use el controlador de relleno en la esquina inferior de la celda de fórmula y aplique la fórmula a la columna.
Convertir texto a fecha usando el método de buscar y reemplazar
Este método usa delimitadores para cambiar el formato del texto a fechas. Si el día, mes y año en sus fechas están separados por algún delimitador que no sea un guión (-) o una barra (/), Excel no los reconocerá como fechas y los almacenará como texto.
Para solucionar este problema, utilice la función Buscar y reemplazar. Al cambiar los delimitadores de período no estándar (.) Con barras (/) o guiones (-), Excel cambiará automáticamente los valores a fechas.
Primero, seleccione todas las fechas de texto que desea convertir en fechas. En la pestaña "Inicio", haga clic en el botón "Buscar y seleccionar" en la esquina más a la derecha de la cinta y seleccione "Reemplazar". Alternativamente, presione Ctrl + H para abrir el cuadro de diálogo Buscar y reemplazar.
En el cuadro de diálogo Buscar y reemplazar, escriba el delimitador que contiene su texto (en nuestro caso, punto (.) En el campo 'Buscar qué' y una barra inclinada (/) o guión (-) en el campo 'Reemplazar con' Haga clic en el botón 'Reemplazar todo' para reemplazar los delimitadores y haga clic en 'Cerrar' para cerrar la ventana.
Ahora, Excel reconoce que sus cadenas de texto ahora son fechas y las formatea automáticamente como fechas. Sus fechas se alinearán a la derecha como se muestra a continuación.
Convertir texto a fecha con la herramienta Pegar especial
Otra forma rápida y fácil de convertir cadenas de texto en fechas es agregando 0 a la cadena de texto usando la opción especial pegar. Agregar cero al valor convierte el texto en el número de serie de la fecha, que puede formatear como fecha.
Primero, seleccione una celda vacía y cópiela (selecciónela y presione Ctrl + C copiar).
Luego, seleccione las celdas que contienen las fechas de texto que desea convertir, haga clic con el botón derecho y seleccione la opción "Pegado especial".
En el cuadro de diálogo Pegado especial, seleccione "Todo" en la sección Pegar, elija "Agregar" en la sección Operación y haga clic en "Aceptar".
También puede realizar otras operaciones aritméticas restar / multiplicar / dividir el valor en la celda de destino con el valor pegado (como multiplicar celdas con 1 o dividir por 1 o restar cero).
Cuando selecciona "Agregar" en Operación, agrega "cero" a todas las fechas de texto seleccionadas, ya que agregar un "0" no cambia los valores, obtendrá el número de serie para cada fecha. Ahora todo lo que tienes que hacer es cambiar el formato de las celdas.
Seleccione los números de serie y en la pestaña "Inicio", haga clic en la lista desplegable "Formato de número" en el grupo Número. Seleccione la opción "Fecha corta" del menú desplegable.
Como puede ver ahora, los números están formateados como fechas y alineados a la derecha.
Convertir texto a fecha usando fórmulas
Hay dos funciones que se utilizan principalmente para convertir texto a la fecha: DATEVALUE y VALUE.
Uso de la función DATEVALUE de Excel
La función DATEVALUE de Excel es una de las formas más fáciles de convertir una fecha representada como texto en el número de serie de una fecha.
La sintaxis de la función DATEVALUE:
= VALOR_FECHA (texto_fecha)Argumento: date_text especifica la cadena de texto que desea ocultar o hacer referencia a la celda que contiene las fechas del texto.
La formula:
= FECHAVALOR (A1)La siguiente imagen ilustra cómo la función DATEVALUE maneja algunos formatos de fecha diferentes que se almacenan como texto.
Obtuvo los números de serie de la fecha, ahora debe aplicar el formato de fecha a estos números. Para hacer eso, seleccione las celdas con números de serie, luego vaya a la pestaña "Inicio" y elija "Fecha corta" en la lista desplegable "Formato de número".
Ahora tiene sus fechas formateadas en la columna C.
Incluso si no hay una parte del año en su fecha de texto (A8), DATEVALUE usará el año actual del reloj de su computadora.
La función DATEVALUE solo convertirá los valores de texto que parecen una fecha. No puede convertir un texto que se parezca a un número hasta la fecha, ni puede cambiar el valor de un número hasta la fecha, para eso necesitará la función VALOR de Excel.
Uso de la función VALOR de Excel
La función VALOR de Excel puede convertir cualquier cadena de texto que se parezca a una fecha o un número en un valor numérico, por lo que es realmente útil cuando se trata de convertir cualquier número, no solo fechas.
La función VALOR:
= VALOR (texto)texto- la cadena de texto que queremos convertir o hacer referencia a la celda que contiene la cadena de texto.
La fórmula de ejemplo para convertir la fecha del texto:
= VALOR (A1)La siguiente fórmula de VALOR puede cambiar cualquier cadena de texto que parezca una fecha en un número como se muestra a continuación.
Sin embargo, la función VALOR no admite todos los tipos de valores de fecha. Por ejemplo, si las fechas usan posiciones decimales (A11), devolverá el # ¡VALOR! error.
Una vez que tenga el número de serie para su fecha, deberá formatear la celda con el número de serie de la fecha para que parezca una fecha, como lo hemos hecho para la función DATEVALUE. Para hacerlo, seleccione los números de serie y seleccione la opción "Fecha" del menú desplegable "Formato de número" en la pestaña "Inicio".
Eso es todo, estas son las 5 formas diferentes en las que puede convertir fechas con formato de texto a fechas en Excel.