Convertir texto a fecha y número a fecha: Simplifica tus datos de forma efectiva
En un mundo cada vez más digital, gestionar la información puede convertirse en un verdadero desafío. ¿Alguna vez has enfrentado la frustración de tener fechas en formato de texto que dificultan tus análisis, o números que deberían representar fechas? No te preocupes, en este artículo descubrirás cómo transformar fácilmente cadenas de texto y números en fechas utilizables, optimizando así tu flujo de trabajo y mejorando tu productividad. Acompáñanos a desentrañar las herramientas y técnicas necesarias para convertir esos datos en información valiosa, y haz que la administración de tus registros nunca haya sido tan sencilla. ¡Sumérgete en el fascinante mundo de la conversión de datos y revoluciona tu forma de trabajar!
¿Sabías que es posible convertir texto a fecha y número a fecha de manera sencilla? Si alguna vez te has preguntado cómo realizar esta conversión en tus proyectos o tareas diarias, estás en el lugar indicado. En este artículo, te mostraremos diferentes métodos y herramientas para convertir texto a fecha y número a fecha de forma rápida y eficiente. Ya no tendrás que preocuparte por errores al ingresar fechas o números en tus aplicaciones o documentos. ¡Sigue leyendo para descubrir cómo hacerlo!
El tutorial explica cómo usar las funciones de Excel para convertir texto a fecha y números a fecha, y cómo convertir cadenas de texto en fechas sin fórmulas. También aprenderá cómo cambiar rápidamente un formato de número a fecha.
Dado que Excel no es la única aplicación con la que trabaja, a veces se encontrará trabajando con fechas importadas en una hoja de cálculo de Excel desde un archivo .csv u otra fuente externa. Cuando eso suceda, es probable que las fechas se exporten como entradas de texto. Aunque parezcan fechas, Excel no las reconocerá como tales.
Hay muchas formas de convertir texto a fecha en Excel y este tutorial tiene como objetivo cubrirlas todas, para que pueda elegir la técnica de conversión de texto a fecha más adecuada para su formato de datos y su preferencia por una forma con o sin fórmula. .
Cómo distinguir las fechas normales de Excel de las «fechas de texto»
Al importar datos a Excel, suele surgir un problema con el formato de la fecha. Las entradas importadas pueden parecerle fechas normales de Excel, pero no se comportan como fechas. Microsoft Excel trata dichas entradas como texto, lo que significa que no puede ordenar su tabla por fecha correctamente, ni puede usar esas «fechas de texto» en fórmulas, tablas dinámicas, gráficos o cualquier otra herramienta de Excel que reconozca fechas.
Hay algunas señales que pueden ayudarle a determinar si una entrada determinada es una fecha o un valor de texto.
fechasValores de texto
- Alineado a la derecha por defecto.
- Tener Fecha formato en el Formato numérico caja en el Hogar pestaña > Número.
- Si se seleccionan varias fechas, la barra de estado muestra Promedio, Contar y SUMA.
- Alineado a la izquierda por defecto.
- General El formato se muestra en el Formato numérico caja en el Hogar pestaña > Número.
- Si se seleccionan varias fechas de texto, la barra de estado solo muestra Contar.
- Es posible que haya un apóstrofo inicial visible en la barra de fórmulas.
>Cómo convertir un número a una fecha en Excel
Dado que todas las funciones de Excel que cambian el texto a la fecha devuelven un número como resultado, primero echemos un vistazo más de cerca a la conversión de números a fechas.
Como probablemente sepa, Excel almacena fechas y horas como números de serie y es sólo el formato de una celda lo que obliga a que un número se muestre como una fecha. Por ejemplo, el 1 de enero de 1900 se almacena como el número 1, el 2 de enero de 1900 se almacena como 2 y el 1 de enero de 2015 se almacena como 42005. Para obtener más información sobre cómo Excel almacena fechas y horas, consulte Fecha de Excel. formato.
Al calcular fechas en Excel, el resultado devuelto por diferentes funciones de fecha suele ser un número de serie que representa una fecha. Por ejemplo, si =HOY()+7 devuelve un número como 44286 en lugar de la fecha que es 7 días después de hoy, eso no significa que la fórmula sea incorrecta. Simplemente, el formato de celda se establece en General o Texto mientras debería ser Fecha.
Para convertir dicho número de serie a la fecha, todo lo que tiene que hacer es cambiar el formato del número de celda. Para esto, simplemente elija Fecha en el Formato numérico caja en el Hogar pestaña.
Para aplicar un formato distinto al predeterminado, seleccione las celdas con números de serie y presione Ctrl+1 para abrir el Formato de celdas diálogo. Sobre el Número pestaña, elija Fechaseleccione el formato de fecha deseado en Tipo y haga clic en Aceptar.
>¡Sí, es así de fácil! Si desea algo más sofisticado que los formatos de fecha predefinidos de Excel, consulte cómo crear un formato de fecha personalizado en Excel.
Si algún número obstinado se niega a cambiar a una fecha, consulte el formato de fecha de Excel que no funciona: consejos para solucionar problemas.
Cómo convertir un número de 8 dígitos a la fecha en Excel
Es una situación muy común cuando se ingresa una fecha como un número de 8 dígitos como 10032016, y es necesario convertirla en un valor de fecha que Excel pueda reconocer (03/10/2016). En este caso, simplemente cambiar el formato de la celda a Fecha no funcionará; obtendrá ########## como resultado.
Para convertir dicho número a la fecha, deberá utilizar la función FECHA en combinación con las funciones DERECHA, IZQUIERDA y MEDIA. Desafortunadamente, no es posible crear una fórmula universal que funcione en todos los escenarios porque el número original se puede ingresar en una variedad de formatos diferentes. Por ejemplo:
NúmeroFormatoFecha10032016ddmmaaaa10-marzo-201620160310aaaammdd20161003aaaaddmm
De todos modos, intentaré explicar el enfoque general para convertir dichos números en fechas y proporcionar algunos ejemplos de fórmulas.
Para empezar, recuerde el orden de los argumentos de la función Fecha de Excel:
FECHA(año, mes, día)
Entonces, lo que debe hacer es extraer un año, mes y fecha del número original y proporcionarlos como argumentos correspondientes a la función Fecha.
Por ejemplo, veamos cómo puede convertir el número 10032016 (almacenado en la celda A1) a la fecha 10/03/2016.
- Extrae el año. Son los últimos 4 dígitos, por lo que usamos la función DERECHA para seleccionar los últimos 4 caracteres: DERECHA (A1, 4).
- Extrae el mes. son los 3tercero y 4th dígitos, por lo que empleamos la función MID para obtenerlos MID(A1, 3, 2). Donde 3 (segundo argumento) es el número inicial y 2 (tercer argumento) es el número de caracteres a extraer.
- Extrae el día. Son los primeros 2 dígitos, por lo que tenemos la función IZQUIERDA para devolver los primeros 2 caracteres: IZQUIERDA (A2,2).
Finalmente, incruste los ingredientes anteriores en la función Fecha y obtendrá una fórmula para convertir un número en fecha en Excel:
=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))
La siguiente captura de pantalla demuestra esto y un par de fórmulas más en acción:
>Preste atención a la última fórmula en la captura de pantalla anterior (fila 6). El número-fecha original (161003) contiene solo 2 caracteres que representan un año (16). Entonces, para obtener el año 2016, concatenamos 20 y 16 usando la siguiente fórmula: 20&LEFT(A6,2). Si no hace esto, la función Fecha devolverá 1916 de forma predeterminada, lo cual es un poco extraño, como si Microsoft todavía viviera en el siglo 20.th siglo 🙂
Nota. Las fórmulas demostradas en este ejemplo funcionan correctamente siempre que todos los numeros desea convertir a fechas siga las mismo patrón.
Cómo convertir texto a fecha en Excel
Cuando detecta fechas de texto en su archivo de Excel, lo más probable es que desee convertir esas cadenas de texto en fechas normales de Excel para poder consultarlas en sus fórmulas para realizar varios cálculos. Y como suele ser el caso en Excel, hay algunas formas de abordar la tarea.
Función DATEVALUE de Excel: cambiar el texto a la fecha
El VALOR FECHA La función en Excel convierte una fecha en formato de texto en un número de serie que Excel reconoce como una fecha.
La sintaxis de DATEVALUE de Excel es muy sencilla:
VALORFECHA(texto_fecha)
Entonces, la fórmula para convertir un valor de texto a la fecha es tan simple como =DATEVALUE(A1)donde A1 es una celda con una fecha almacenada como una cadena de texto.
Debido a que la función DATEVALUE de Excel convierte una fecha de texto en un número de serie, tendrá que hacer que ese número parezca una fecha aplicando el formato de fecha a ello, como comentamos hace un momento.
Las siguientes capturas de pantalla muestran algunas fórmulas DATEVALUE de Excel en acción:
>Función DATEVALUE de Excel: cosas para recordar
Al convertir una cadena de texto en una fecha usando la función DATEVALUE, tenga en cuenta que:
- La información de tiempo en las cadenas de texto se ignora, como puede ver en las filas 6 y 8 anteriores. Para convertir valores de texto que contienen fechas y horas, utilice el función VALOR.
- Si se omite el año en una fecha de texto, DATEVALUE de Excel seleccionará el año actual del reloj del sistema de su computadora, como se demuestra en la fila 4 anterior.
- Dado que Microsoft Excel almacena fechas desde el 1 de enero de 1900, el uso de la función VALORFECHA de Excel en fechas anteriores dará como resultado el valor #¡VALOR! error.
- La función FECHAVALOR no puede convertir un valor numérico a una fecha, ni puede procesar una cadena de texto que parece un número, para eso necesitará usar la función VALOR de Excel, y esto es exactamente lo que vamos a discutir a continuación.
Función VALOR de Excel: convierte una cadena de texto a la fecha
En comparación con FECHAVALOR, la función VALOR de Excel es más versátil. Puede convertir cualquier cadena de texto que parezca una fecha o un número en un número, que puede cambiar fácilmente al formato de fecha que elija.
La sintaxis de la función VALOR es la siguiente:
VALOR(texto)
Dónde text es una cadena de texto o referencia a una celda que contiene el texto que desea convertir en número.
La función VALOR de Excel puede procesar ambos fecha y horaeste último se convierte a una porción decimal, como puedes ver en la fila 6 en la siguiente captura de pantalla:
>Operaciones matemáticas para convertir texto en fechas.
Además de utilizar funciones específicas de Excel como VALOR y FECHAVALOR, puede realizar una operación matemática simple para obligar a Excel a realizar una conversión de texto a fecha por usted. La condición requerida es que una operación no debe cambiar el valor de la fecha (número de serie). ¿Suena un poco complicado? ¡Los siguientes ejemplos facilitarán las cosas!
Suponiendo que la fecha de su texto está en la celda A1, puede usar cualquiera de las siguientes fórmulas y luego aplicar el formato de fecha a la celda:
- Suma: =A1 + 0
- Multiplicación: =A1 * 1
- División: =A1 / 1
- Doble negación: =–A1
>Como puede ver en la captura de pantalla anterior, las operaciones matemáticas pueden convertir fechas (filas 2 y 4), horas (fila 6) y números formateados como texto (fila 8). A veces, el resultado incluso se muestra automáticamente como una fecha y no tienes que preocuparte por cambiar el formato de la celda.
Cómo convertir cadenas de texto con delimitadores personalizados en fechas
Si las fechas de su texto contienen algún delimitador que no sea una barra diagonal (/) o un guión (-), las funciones de Excel no podrán reconocerlas como fechas y devolver el #VALOR. error.
Para solucionar este problema, puede ejecutar Excel Encontrar y reemplazar herramienta para reemplazar su delimitador con una barra (/), todo de una vez:
- Seleccione todas las cadenas de texto que desee convertir en fechas.
- Prensa Ctrl+H para abrir el Encontrar y reemplazar caja de diálogo.
- Ingrese su separador personalizado (un punto en este ejemplo) en el Encontrar que campo, y una barra en el Reemplazar con
- Haga clic en el Reemplaza todo
>Ahora el VALOR FECHA o VALOR La función no debería tener problemas para convertir las cadenas de texto en fechas. De la misma manera, puedes fijar fechas que contengan cualquier otro delimitador, por ejemplo, un espacio o una barra invertida.
Si prefiere una solución de fórmula, puede utilizar la función SUSTITUIR de Excel en lugar de Reemplaza todo para cambiar sus delimitadores a barras.
Suponiendo que las cadenas de texto están en la columna A, una fórmula SUSTITUCIÓN puede tener el siguiente aspecto:
=SUBSTITUTE(A1, «.», «https://www.ablebits.com/»)
Donde A1 es una fecha de texto y «.» es el delimitador con el que están separadas sus cadenas.
Ahora, incorporemos esta función SUSTITUIR en la fórmula VALOR:
=VALUE(SUBSTITUTE(A1, «.», «https://www.ablebits.com/»))
Y convierta las cadenas de texto en fechas, todo con una única fórmula.
>Como puede ver, las funciones VALOR y FECHA de Excel son bastante poderosas, pero ambas tienen sus límites. Por ejemplo, si está intentando convertir cadenas de texto complejas como Jueves, 01 de enero de 2015, Ninguna función podría ayudar. Afortunadamente, existe una solución sin fórmula que puede realizar esta tarea y la siguiente sección explica los pasos detallados.
Asistente de texto a columnas: forma sin fórmulas de convertir texto hasta la fecha
Si no es un usuario de fórmulas, una función de Excel de larga data llamada Texto a columnas será útil. Puede manejar fechas de texto simples demostradas en Ejemplo 1 así como cadenas de texto de varias partes que se muestran en Ejemplo 2.
Ejemplo 1. Convertir cadenas de texto simples en fechas
Si las cadenas de texto que desea convertir en fechas se parecen a cualquiera de las siguientes:
- 1.1.2015
- 1.2015
- 01 01 2015
- 2015/1/1
Realmente no necesitas fórmulas, ni exportar ni importar nada. Todo lo que necesitas son 5 pasos rápidos.
En este ejemplo, convertiremos cadenas de texto como 01 01 2015 (día, mes y año están separados por espacios) en fechas.
>
>En el paso 2 del asistente, desmarque todas las casillas delimitadoras y haga clic en Próximo.
>En el paso final, seleccione Fecha bajo formato de datos de columna, elige el formato correspondiente a tus fechasy haga clic Finalizar.
En este ejemplo, estamos convirtiendo las fechas de texto con el formato «01 02 2015» (mes día año), por lo que seleccionamos MDY desde el cuadro desplegable.
>Ahora, Excel reconoce sus cadenas de texto como fechas, las convierte automáticamente a su formato de fecha predeterminado y las muestra alineadas a la derecha en las celdas. Puede cambiar el formato de fecha de la forma habitual a través del Formato de celdas diálogo.
Nota. Para el Texto a columna Para que el asistente funcione correctamente, todas las cadenas de texto deben tener el mismo formato. Por ejemplo, si algunas de sus entradas tienen el formato como día mes año formato mientras que otros son mes día añoobtendría resultados incorrectos.
Ejemplo 2. Convertir cadenas de texto complejas en fechas
Si sus fechas están representadas por cadenas de texto de varias partes, como por ejemplo:
- jueves, 01 de enero de 2015
- 01 de enero de 2015 15:00
Tendrás que esforzarte un poco más y utilizar tanto el Texto a columnas asistente y función FECHA de Excel.
Por ejemplo, si está convirtiendo cadenas separadas por comas y espacios, como «Jueves 01 de enero de 2015″debes elegir ambos delimitadores: coma y espacio.
> También tiene sentido seleccionar el «Trate los delimitadores consecutivos como uno solo«Opción para ignorar espacios adicionales, si sus datos tienen alguno.
Y por último, echa un vistazo a Vista previa de datos ventana y verifique si las cadenas de texto están divididas en columnas correctamente, luego haga clic en Próximo.
Nota. No elijas el Fecha formato para cualquier columna porque cada columna contiene solo un componente, por lo que Excel no podrá entender que se trata de una fecha.
Si no necesita alguna columna, haga clic en ella y seleccione No importar columna (omitir).
Si no desea sobrescribir los datos originales, especifique dónde se deben insertar las columnas: ingrese la dirección de la celda superior izquierda en el Destino campo.
Cuando termine, haga clic en el Finalizar botón.
> Como puede ver en la captura de pantalla anterior, nos saltamos la primera columna con los días de la semana y dividimos los demás datos en 3 columnas (en el General formato) e insertando estas columnas comenzando desde la celda C2.
La siguiente captura de pantalla muestra el resultado, con los datos originales en la columna A y los datos divididos en las columnas C, D y E.
>
FECHA(año, mes, día)
En nuestro caso, year está en la columna E y day está en la columna D, no hay problema con estos.
No es tan fácil con month porque es texto mientras que la función FECHA necesita un número. Afortunadamente, Microsoft Excel proporciona una función MES especial que puede cambiar el nombre de un mes por el número de un mes:
=MONTH(serial_number)
Para que la función MES entienda que se trata de una fecha, la expresamos así:
=MONTH(1&C2)
Donde C2 contiene el nombre del mes, Enero en nuestro caso. Se agrega «1&» para concatenar una fecha (Enero 1) para que la función MES pueda convertirlo al número de mes correspondiente.
Y ahora, incorporemos la función MES en el month; argumento de nuestra fórmula FECHA:
=DATE(F2,MONTH(1&D2),E2)
Y listo, nuestras complejas cadenas de texto se convierten exitosamente en fechas:
>Conversión rápida de fechas de texto usando Pegado especial
Para convertir rápidamente una variedad de cadenas de texto simples en fechas, puede utilizar el siguiente truco.
- Copie cualquier celda vacía (selecciónela y presione Ctrl+C).
- Seleccione el rango con valores de texto que desea convertir en fechas.
- Haga clic derecho en la selección, haga clic Pegado especialy seleccione Agregar en el Pegado especial caja de diálogo:
>Hacer clic DE ACUERDO para completar la conversión y cerrar el cuadro de diálogo.
Lo que acaba de hacer es decirle a Excel que agregue un cero (celda vacía) a las fechas de su texto. Para poder hacer esto, Excel convierte una cadena de texto en un número y, dado que agregar un cero no cambia el valor, obtienes exactamente lo que deseas: el número de serie de la fecha. Como siempre, tu cambiar un número al formato de fecha usando el Formato de celdas diálogo.
Para obtener más información sobre la función Pegado especial, consulte Cómo usar Pegado especial en Excel.
Corrección de fechas de texto con años de dos dígitos
Las versiones modernas de Microsoft Excel son lo suficientemente inteligentes como para detectar algunos errores obvios en sus datos, o mejor dicho, lo que Excel considera un error. Cuando esto suceda, verá un indicador de error (un pequeño triángulo verde) en la esquina superior izquierda de la celda y cuando seleccione la celda, aparecerá un signo de exclamación:
>Al hacer clic en el signo de exclamación se mostrarán algunas opciones relevantes para sus datos. En el caso de un año de 2 dígitos, Excel le preguntará si desea convertirlo a 19XX o 20XX.
Si tiene varias entradas de este tipo, puede corregirlas todas de una sola vez: seleccione todas las celdas con errores, luego haga clic en el signo de exclamación y seleccione la opción adecuada.
>Cómo activar la comprobación de errores en Excel
Por lo general, la comprobación de errores está habilitada en Excel de forma predeterminada. Para asegurarse, haga clic Archivo > Opciones > Fórmulasdesplácese hacia abajo hasta Comprobación de errores y verifique si las siguientes opciones están marcadas:
- Habilitar la verificación de errores en segundo plano bajo Comprobación de errores;
- Celdas que contienen años representados como 2 dígitos bajo Reglas de verificación de errores.
>Cómo cambiar el texto a la fecha en Excel de una manera fácil
Como puede ver, convertir texto a una fecha en Excel está lejos de ser una operación trivial con un solo clic. Si está confundido por los diferentes casos de uso y fórmulas, permítame mostrarle una forma rápida y sencilla.
Instale nuestra Ultimate Suite (puede descargar una versión de prueba gratuita aquí), cambie a Herramientas Ablebits pestaña (¡se agregarán 2 pestañas nuevas que contienen más de 70 herramientas increíbles a su Excel!) y busque la Texto hasta la fecha botón:
>Para convertir fechas de texto en fechas normales, esto es lo que debe hacer:
¡Eso es todo! Los resultados de la conversión aparecerán en la columna adyacente, sus datos de origen se conservarán. Si algo sale mal, simplemente puede eliminar los resultados y volver a intentarlo con un orden de fechas diferente.
>
Consejo. Si elige convertir horas y fechas, pero faltan unidades de tiempo en los resultados, asegúrese de aplicar un formato numérico que muestre los valores de fecha y hora. Para obtener más información, consulte Cómo crear formatos de fecha y hora personalizados.
Si tiene curiosidad por saber más sobre esta maravillosa herramienta, consulte su página de inicio: Text to Date for Excel.
Así es como se convierte texto a fecha en Excel y se cambian fechas a texto. Esperemos que hayas podido encontrar una técnica de tu agrado. En el próximo artículo, abordaremos la tarea opuesta y exploraremos diferentes formas de convertir fechas de Excel en cadenas de texto. Te agradezco por leer y espero verte la próxima semana.
Gongora: ¡Exacto, Elorriaga! Yo solía rendirme cuando veía esos formatos extraños, pero desde que utilicé esos consejos, ¡todo es un paseo! Me acuerdo que en una presentación, me surgió la misma inquietud y al final logré que todo cuadrara. ¡Es una gran satisfacción! ¡Gracias por ponerlo en palabras!
Elorriaga: ¡Sí, es cierto! A mí también me pasaba lo mismo, siempre pensando que era un lío, pero cuando me lancé a hacerlo con esa herramienta que sugieres, todo cambió. Recuerdo que en mi trabajo anterior tenía que lidiar con un montón de reportes llenos de fechas y era un desastre, pero ahora ya no hay problema. ¡Mil gracias por lo que compartes!
Castander: ¡Buen artículo! Siempre me ha parecido complicado convertir textos y números a fechas, pero probé lo que mencionaste y, ¡funcionó de maravilla! La verdad, una vez me volvió loco cuando intenté hacer cálculos con fechas en un proyecto y terminé perdido. ¡Gracias por la ayuda!
Domecq: ¡Totalmente de acuerdo, castander! A mí también me parecía un rollo, pero una vez que le agarras la onda, es pan comido. Me acuerdo que en la uni tenía que convertir un montón de fechas y era un caos, pero ahora es mucho más fácil. ¡Gracias por compartir esto!