Excel almacena los valores de fecha y hora como números de serie en el back-end.
Esto significa que, si bien puede ver una fecha como "10 de enero de2021-2022" o "01/10/2020" en una celda, en el back-end, Excel lo almacena como un número. Esto es útil ya que también permite a los usuarios sumar / restar fácilmente la fecha y la hora en Excel.
En Microsoft Excel para Windows, "01 de enero de 1900" se almacena como 1, "02 de enero de 1900" se almacena como 2, y así sucesivamente.
A continuación, ambas columnas tienen los mismos números, pero la Columna A muestra números y la Columna B muestra la fecha que está representada por ese número.
Esta es también la razón por la que a veces puede esperar una fecha en una celda pero termina viendo un número. Veo que esto sucede todo el tiempo cuando descargo datos de bases de datos o herramientas basadas en la web.
Ocurre cuando el formato de celda está configurado para mostrar un número como un número en lugar de una fecha.
Entonces, ¿cómo podemos convertir estos números de serie en fechas?
De eso se trata este tutorial.
En este tutorial, le mostraría dos formas realmente fáciles de convertir números de serie en fechas en Excel
Nota: Excel para Windows usa el sistema de fechas 1900 (lo que significa que 1 representaría el 1 de enero de 1900, mientras que Excel para Mac usa el sistema de fechas 1904 (que para hombres representa el 1 de enero de 1904 en Excel en Mac)¡Entonces empecemos!
Convertir números de serie en fechas con formato de número
La forma más sencilla de convertir un número de serie de fecha en una fecha es cambiando el formato de las celdas que tienen estos números.
Puede encontrar algunos de los formatos de fecha más utilizados en la pestaña Inicio en la cinta
Déjame mostrarte cómo hacer esto.
Uso de las opciones de formato de fecha incorporadas en la cinta de opciones
Suponga que tiene un conjunto de datos como se muestra a continuación y desea convertir todos estos números en la columna A en la fecha correspondiente que representa.
A continuación se muestran los pasos para hacer esto:
- Seleccione las celdas que tienen el número que desea convertir en una fecha
- Haga clic en la pestaña "Inicio".
- En el grupo "Número", haga clic en el menú desplegable Formato de número
- En el menú desplegable, seleccione la opción "Fecha larga" o Fecha corta "(según el formato en el que desee que estén estos números)
¡Eso es!
Los pasos anteriores convertirían los números al formato de fecha seleccionado.
Los pasos anteriores no han cambiado el valor de la celda, solo la forma en que se muestra.
Tenga en cuenta que Excel toma el formato de fecha corta según la configuración regional de su sistema. Por ejemplo, si se encuentra en EE. UU., El formato de fecha sería MM / DD / AAAA, y si se encuentra en el Reino Unido, el formato de fecha sería DD / MM / AAAA.
Si bien este es un método rápido para convertir números de serie en fechas, tiene un par de limitaciones:
- Solo hay dos formatos de fecha: fecha corta y fecha larga (y eso también en un formato específico). Por lo tanto, si solo desea obtener el mes y el año y no el día, no podrá hacerlo con estas opciones.
- No puede mostrar la fecha y la hora con las opciones de formato del menú desplegable. Puede elegir mostrar la fecha o la hora, pero no ambas.
Por lo tanto, si necesita más flexibilidad en la forma en que desea mostrar las fechas en Excel, debe usar la opción Formato de número personalizado (que se describe a continuación en este tutorial).
A veces, cuando convierte un número en una fecha en Excel, en lugar de la fecha, es posible que vea algunos signos de almohadilla (algo como #####). esto sucede cuando el ancho de la columna no es suficiente para acomodar la fecha completa. En tal caso, simplemente aumente el ancho de la columna.Creación de un formato de fecha personalizado mediante el cuadro de diálogo Formato de número
Suponga que tiene un conjunto de datos como se muestra a continuación y desea convertir los números de serie en la columna A en fechas en el siguiente formato: 01.01.2020
Tenga en cuenta que esta opción no está disponible de forma predeterminada en el método de cinta que cubrimos anteriormente.
A continuación se muestran los pasos para hacer esto:
- Seleccione las celdas que tienen el número que desea convertir en una fecha
- Haga clic en la pestaña "Inicio".
- En el grupo Números, haz clic en el ícono del selector del cuadro de diálogo (es una pequeña flecha inclinada en la parte inferior derecha del grupo)
- En el cuadro de diálogo "Formato de celdas" que se abre, asegúrese de que la pestaña "Número" esté seleccionada
- En las opciones de "Categoría" a la izquierda, seleccione "Fecha"
- Seleccione el formato deseado en el cuadro "Tipo".
- Haga clic en Aceptar
Los pasos anteriores convertirían los números al formato de fecha seleccionado.
Como puede ver, hay más opciones de formato de fecha en este caso (en comparación con la fecha corta y larga que obtuvimos con la cinta).
Y en caso de que no encuentre el formato que busca, también puede crear su propio formato de fecha.
Por ejemplo, digamos que quiero mostrar la fecha en el siguiente formato: 01/01/2020 (que aún no es una opción en el cuadro de diálogo de formato de celdas).
Así es como puedo hacer esto:
- Seleccione las celdas que tienen los números que desea convertir en una fecha
- Haga clic en la pestaña Inicio
- En el grupo Números, haga clic en el ícono del iniciador del cuadro de diálogo (es una pequeña flecha de inclinación en la parte inferior derecha del grupo)
- En el cuadro de diálogo "Formato de celdas" que se abre, asegúrese de que la pestaña "Número" esté seleccionada
- En las opciones de "Categoría" de la izquierda, seleccione "Personalizado".
- En el campo de la derecha, ingrese
mm / dd / aaaa
- Haga clic en Aceptar
Los pasos anteriores cambiarían todos los números al formato de número especificado.
Con el formato personalizado, obtiene el control total y le permite mostrar la fecha en el formato que desee. También puede crear un formato en el que muestre la fecha y la hora en la misma celda.
Por ejemplo, en el mismo ejemplo, si desea mostrar la fecha y la hora, puede usar el siguiente formato:
mm / dd / aaaa hh: mm AM / PM
A continuación se muestra la tabla que muestra los códigos de formato de fecha que puede utilizar:
| Código de formato de fecha | Cómo formatea la fecha |
| metro | Muestra el mes como un número del 1 al 12 |
| mm | Muestra los meses como números de dos dígitos del 01 al 12 |
| mmm | Muestra el mes como tres letras como en enero-diciembre |
| mmmm | Muestra el nombre completo de los meses como en enero-diciembre |
| mmmmm | Muestra el primer alfabeto del nombre del mes como en J-D |
| D | Muestra los días como 1-31 |
| dd | Muestra los días como 01-31 |
| ddd | Muestra los días como Sun-Sat |
| dddd | Muestra los días como de domingo a sábado |
| aa | Muestra años como 00-99 |
| aaaa | Muestra años como 1900-9999 |
Convertir números de serie en fechas usando la fórmula de TEXTO
Los métodos cubiertos anteriormente funcionan cambiando el formato de las celdas que tienen los números.
Pero en algunos casos, es posible que no pueda utilizar estos métodos.
Por ejemplo, a continuación tengo un conjunto de datos donde los números tienen un apóstrofo inicial, que convierte estos números en texto.
Si intenta utilizar las opciones incorporadas para cambiar el formato de las celdas con este conjunto de datos, no hará nada. Esto se debe a que Excel no los considera números y, dado que las fechas son números, Excel rechaza su deseo de formatearlos.
En tal caso, puede convertir este texto en números y luego usar los métodos de formato mencionados anteriormente, o usar otra técnica para convertir números de serie en fechas: usando la función TEXTO.
La función TEXTO toma dos argumentos: el número que debe formatearse y el código de formato.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea convertir todos los números y la columna A en fechas.
Debajo de la fórmula que podría hacer eso:
= TEXTO (A2, "MM / DD / AAAA")
Tenga en cuenta que en la fórmula he especificado el formato de fecha en el formato MM / DD / AAAA. Si necesita utilizar cualquier otro formato, puede especificar el código como segundo argumento de la fórmula TEXT.
También puede utilizar esta fórmula para mostrar la fecha y la hora.
Por ejemplo, si desea que el resultado final tenga el formato: 01/01/2020 12:00 a. M., Utilice la siguiente fórmula:
= TEXTO (A2, "mm / dd / aaaa hh: mm AM / PM")
En la fórmula anterior, también agregué el formato de hora, por lo que si hay partes decimales en los números, se mostrará como la hora en horas y minutos.
Si solo desea la fecha (y no la fórmula subyacente), convierta las fórmulas en valores estáticos usando pegar especial.
Una gran ventaja de usar la función TEXTO para convertir números de serie en fechas es que puede combinar el resultado de la fórmula TEXTO con otras fórmulas. Por ejemplo, puede combinar la fecha resultante con el texto y mostrar algo como: La fecha de vencimiento es el 1 de enero de 2020. o La fecha límite es el 01 de enero de 2020
Nota: Recuerde que las fechas y la hora se almacenan como números, donde cada número entero representaría un día completo y la parte decimal representaría esa parte del día. Entonces, 1 significaría 1 día completo y 0.5 significaría medio día o 12 horas.
Estas son dos formas sencillas que puede utilizar para convertir números de serie en fechas en Microsoft Excel.
¡Espero que este tutorial te haya resultado útil!
