Conocer la forma en que Excel maneja las fechas y las horas internamente te permitirá entender algunas situaciones que suelen despistar a los más novatos y obtener mejores resultados.
Fechas
Hacer cálculos con fechas sin ayuda de un calendario no es muy fácil. Si quieres comprobarlo, intenta calcular, por ejemplo, la cantidad de días transcurridos entre el 08/11/2013 y el 14/08/2016 mentalmente o solo con lápiz y papel.
Pero Excel tiene una forma muy práctica de manejar las fechas: las numera.
Internamente, Excel almacena las fechas como números enteros secuenciales a partir del 1 de enero de 1900. Esa es la primera fecha válida para Excel y la última es el 31/12/9999 cuyo número es el 2958465. Las fechas anteriores al 01/01/1900 no son reconocidas como tales por Excel.
Por lo tanto, el 01/01/1900 para Excel es la fecha número 1; el 02/01/1900 es la 2; el 31/01/1900 es la 31; el 01/02/1900 es la 32 y así sucesivamente. El 12/12/2012 es el día número 41255 para Excel; el 08/11/2013 (la fecha en que se publicó este artículo por primera vez), corresponde al número 41586.
Los números internos que Excel le asigna a las fechas se llaman números de serie.
Pero Excel, como los buenos prestidigitadores, no nos deja ver ese truco. Cuando nosotros ingresamos una fecha en una celda que tiene el formato General, Excel la sustituye por su número correspondiente y, de inmediato, le aplica a la celda el formato Fecha. Todo es tan rápido que, si no conocemos el truco, no nos damos cuenta de nada.
Sin embargo, todo lo dicho hasta ahora se puede comprobar muy fácilmente: si ingresas una fecha en una celda y luego le aplicas el formato General, la fecha se mostrará como un número entero. Algo similar sucede si hacemos el proceso inverso: si ingresas un número entero en una celda y luego le aplicas un formato de fecha, el número se mostrará con el formato de fecha aplicado.
Y esto nos lleva al punto clave: si le pedimos a Excel que sume 45 días a la fecha 18/02/2017, simplemente sumará 42784+45, porque internamente el 18/02/2017 tiene el número 42748. 42784+45 nos da 42829, que es el número correspondiente al 04/04/2017.
¿Qué ventajas nos da saber esto?
En primer lugar, podemos resolver los cálculos con fechas como si se tratara de operaciones aritméticas simples de números enteros. Es decir, podemos sencillamente sumar o restar días a una fecha para obtener la fecha resultante, sin tener que lidiar con los meses y los años. También podemos restar dos fechas para saber la cantidad de días que hay entre ellas.
Además, saber que Excel maneja las fechas internamente como números de serie nos ayuda a entender por qué a veces esperamos una fecha y obtenemos un número o esperamos un número y obtenemos una fecha. Es solamente un problema relacionado con el formato de número asignado a la celda, que se soluciona cambiándolo de General a Fecha o viceversa.
Por último, dejará de ser un misterio el argumento "núm_de_serie" que aparece en muchas funciones de fecha. Por ejemplo, la función MES da como resultado el número del mes de la fecha que se le ingresa como argumento. Pero la guía que ofrece Excel nos dice que debemos usar como argumento un "núm_de_serie", que para la mayoría de los usuarios es un misterio.
El núm_de_serie al que se hace referencia es, obviamente, el número de serie interno de la fecha que deseamos usar como argumento.
Pero no es necesario que sepamos el número de serie de una fecha para usar esta función. Si usamos la referencia a una celda que contiene una fecha, Excel obtendrá el número de serie por sí mismo.
Puede que te preguntes por qué la guía de Excel pide un "núm_de_serie" en lugar de pedir, lisa y llanamente, una "fecha". Yo tampoco lo sé. Pero supongo que es para evitar que los usuarios escriban una fecha dentro de la fórmula del mismo modo que lo harían normalmente en una celda, como: =MES(12/12/2012). Esta fórmula devuelve un 1 (enero) en lugar de un 12 (diciembre), que es lo que se esperaría, porque Excel interpreta esta fecha como una división (12 dividido 12, dividido 2012), que da cero. Curiosamente, para Excel la fecha 0 cae en enero (más abajo en este artículo volveremos sobre esto).
Sin embargo, Excel sí acepta las fechas escritas entre comillas como argumentos de funciones, por ejemplo: =MES("12/12/2012").
Algunos ejemplos prácticos
Veamos ahora la sencillez con la que se pueden resolver los siguientes problemas en Excel, una vez que sabemos que las fechas en realidad son números enteros.
Ejemplo 1
Problema: Calcular la fecha de vencimiento de una factura a 60 días.
Solución: Sumar 60 a la fecha de factura.
Ejemplo 2
Problema: Averiguar cuántos días faltan para que termine el año.
Solución: Restar a la fecha del último día del año la fecha de hoy.
Ejemplo 3
Problema: Obtener el importe por un alquiler de $500 diarios.
Solución: Restar a la fecha del último día de alquiler la del primer día y multiplicar ese resultado por 500.
Horas
Como es de esperar, Excel utiliza un sistema similar al que usa con las fechas para manejar las horas.
Partiendo de la base de que las horas son fracciones del día, Excel representa las horas como números decimales entre cero y uno.
La hora 12:00:00 (el mediodía) es considerado por Excel internamente como 0,5, ya que, en un día de 24 horas, 12 horas representan la mitad. La hora 6:00:00, equivale para Excel al número 0,25 (la cuarta parte del día) y así.
Ya que las fechas son representadas por números enteros, no es difícil entender que las horas se expresen con números decimales, pues una hora es simplemente una porción del día.
Como un día se compone de 24 horas, para Excel, una hora equivale a 1/24, es decir: 0,0416666666666667. Si ingresas el número 0,0416666666666667 en una celda y luego le aplicas el formato de Hora, el número se mostrará como la hora 1:00:00.
De esta manera, Excel tiene la capacidad de representar la fecha y hora de un acontecimiento como un único número, donde su parte entera representa al día y su parte decimal a la hora.
El número de serie 42784,567, por ejemplo, equivale al 18/02/2017 a las 13:36:29.
Algunas curiosidades
Si ingresamos el número cero en una celda y luego le aplicamos un formato de fecha, Excel devolverá: 00/01/1900. Una fecha inexistente, pero curiosamente funcional (si sumamos, por ejemplo, 10 días al 00/01/1900, el resultado será la fecha 10/01/1900).
Si aplicamos un formato de fecha u hora sobre un número negativo, el resultado será una serie de signos numerales o almohadillas: #######, que indican que, como es lógico, no es posible para Excel manejar fechas ni horas negativas.
Otra fecha inexistente que acepta Excel es el 29/02/1900 (el año 1900 no fue bisiesto). Pero este error no partió de Excel sino de Lotus 1-2-3, que era el software de hojas de cálculo que dominaba el mercado cuando apareció Excel. Microsoft decidió mantener el error para ser compatible con Lotus y, de este modo, evitarles un problema a los nuevos usuarios de Excel que quisieran seguir usando las planillas creadas con el software de la competencia.
También por motivos de compatibilidad, Excel para Mac utiliza un sistema de fechas distinto, que comienza el 01/01/1904. Excel para Windows puede ser configurado para limitar el ingreso de fechas a partir de 1904, en lugar de hacerlo desde el 01/01/1900. Es una modificación que se aplica solo a nivel de libro y no afecta a la aplicación entera. Sin embargo, la gran mayoría de las veces este ajuste es totalmente innecesario.
Atajos de teclado:
Podemos insertar la fecha actual en una celda usando el atajo de teclado CTRL+, (esto significa presionar la tecla CTRL y mantenerla presionada hasta presionar la coma).
Para ingresar la hora actual, debemos presionar CTRL+: (La hora ingresada mediante este atajo tendrá implícita la fecha del día, como podremos comprobar si aplicamos a la celda un formato de Fecha.)
¿Te pareció útil esta información? Si es así, te invito a suscribirte a nuestro Boletín gratuito, para mantenerte al tanto de los artículos y noticias más relevantes que se publiquen en el blog.
La imagen superior de este artículo se basa en un diseño de Freepik.
Hola.
¿Cómo puedo hacer para que no aparezca la fecha 01/01/1900 en las celdas vacías de una columna, la columna B (VENCE), donde he arrastrado la siguiente formula: =FECHA.MES(A1;+1)?
Necesito que aparezcan resultados en B solamente cuando A (FECHA) cuente con alguna fecha y así sucesivamente al resto de la columna. Actualmente, cuando arrastro mi formula al resto de la columna B, aparece la fecha 01/01/1900 en las celdas B2, B3, etc. y las necesito todas en blanco si A2, A3, etc. también lo están.
Se trata de sumar 30 días a una fecha de factura de la columna A (FECHA) para calcular su fecha de vencimiento en la columna B (VENCE), pero solamente cuando esté indicada una fecha de referencia en la columna A Ej.: Fecha de factura en la celda A1: 30/09/2023; una vez aplicada la fórmula en la celda B1: =FECHA.MES(A1;+1), el resultado de la celda B1 será: 29/10/2023.
La cuestión es que, si en la celda A2 no hay datos, tampoco quiero que aparezca nada en la celda B2 (actualmente aparece 01/01/1900) y así sucesivamente para el resto de la columna hasta el final de la tabla.
En definitiva, necesito que se aplique la fórmula en la columna VENCE solamente cuando detecte datos en la columna FECHA.
Gracias de antemano por su ayuda.
Hola, Thomas. ¿Cómo estás?
Podrías usar esta fórmula en B1:
=SI(A1<>""; FECHA.MES(A1;1); "").
Luego, simplemente, deberás arrastrar la fórmula hasta la fila que necesites.
Espero te sirva.
¡Saludos!
Hola, tengo una consulta. Es que tengo un rango de fecha una inicio y otra de fin y quería saber cuántos días le corresponde a cada mes. Por ejemplo: 1/8/2023 al 12/12/2023, necesito saber cuantos días le corresponden a cada mes lo he hecho con la función SI pero no me devuelve los días exactos en algunos meses. Quería saber si me podría ayudar con eso. Gracias.
Hola, Elizabeth.
En principio, la cantidad de días transcurridos entre dos fechas se puede obtener restando la fecha mayor a la menor.
Por ejemplo, si en A1 tuvieras la fecha de inicio y en B1, la de fin, podrías poner en otra celda la fórmula "=B1-A1", para obtener la cantidad de días transcurridos.
Pero necesitaría más detalles de la hoja que estás usando para poder darte una respuesta más exacta.
Saludos.
Saludos, compañeros:
Todo lo anterior es muy interesante y está explicado de manera sencilla y precisa; pero no he encontrado solución a mi problema, que es el siguiente:
Introduzco una fecha desde una hoja tal y la mando a otra, la cual no registra el dato enviado, sino la fecha 00/01/1900.
Ojalá pudieran ayudarme, he tratado de varias maneras sin resultado positivo.
Gracias.
Hola, Tomás. ¿Qué tal?
Es un poco raro lo que te está ocurriendo, es cierto.
¿De qué manera estás mandando la fecha de una hoja a la otra?
Saludos.
Hola. buen día. ¿Cómo hago para que Excel me coloque un número asignado por mí, de acuerdo a unos rangos en horas? Por ejemplo:
tengo rangos de horas organizados así: de las 3:00 a las 8:59 rango1, de las 9:00 a las 14:59 rango2, de las 15:00 a las 20:59 rango3.
Tengo una columna que tiene diferentes horas, necesito crear una columna que me diga en que rango quedo "x" hr, asi:
|3:40| | 1 | teniendo en cuenta que las 3:40 esta dentro del rango1.
|6:42| | 1 | teniendo en cuenta que las 6:42 esta dentro del rango 1
|11:51| | 2 | teniendo en cuenta que las 11:51 esta dentro del rango2
Traté de utilizar la función BUSCARV creando una tabla con los rangos en otra pestaña, pero no sé si debo colocar desde las 3:00, 3:01, 3:02, 3:03 y así hasta las 8:59.
Tu ayuda sería valiosa. Muchas gracias.
Hola, Kelly. ¿Cómo estás'
Podrías usar BUSCARV poniendo como último argumento VERDADERO, en vez de FALSO.
Las fechas deben estar en orden ascendente.
Con eso solucionas el problema.
Espero te sirva.
Saludos.
Buenos días, tengo un Excel con un registro de entrada en formato hora (11:10) pero, cuando hago la tabla dinámica, ese campo me lo devuelve como <00/01/1900.
Gracias por adelantado por vuestra ayuda.
Saludos.
Hola, Cristina.
Posiblemente, el problema se soluciones si le aplicas formato de hora a las celdas que mencionas.
Dime si con esto se arregla.
¡Saludos!
Una ayuda muy interesante.
A ver si podéis ayudarme con este problema: necesito la misma fecha de hoy pero del mes anterior.
Gracias
Hola, Luis.
Para lograr lo que planteas, puedes usar la función FECHA.MES.
Suponiendo que la fecha de hoy está en la celda A1, la fórmula quedaría así:
=FECHA.MES(A1; -1)
El primer argumento es la fecha de partida.
El segundo argumento indica cuántos meses quieres moverte hacia adelante o hacia atrás, desde la fecha de partida.
Espero te sirva.
Saludos.
Tengo una base de datos donde hay dos columnas: fecha inicial y fecha final. Al hacer la tabla dinámica y querer poner la fecha inicial con un filtro de fecha minima, me salen todos las fechas 01/01/1900. ¿Cómo soluciono esto??????????????
Hola, Oscar.
Evidentemente, Excel no está reconociendo como válidas las fechas del origen de datos.
Lo más probable es que las esté interpretando como textos.
Pero sin ver la hoja no me es posible darte ninguna solución, lamentablemente.
No hay una solución única para todos los casos.
Una posibilidad válida para transformar textos a números es multiplicar *1. Si posees una fecha en la celda A1 que Excel reconoce como texto, puedes intentar en una celda contigua o a elección simplemente =A1*1. Automáticamente en esa celda, el formato para Excel será fecha y no texto. Aplica para cualquier tipo de formato: puedes multiplicar horas, etc. Saludos.
Hola. Quiero hacer que, en una tabla con algunas fechas del año, al poner la fecha actual en una celda me proporcione, en la celda de a lado, el número de fechas que hay de los últimos 30 días. Si me pudieras ayudar, por favor.
Hola, An.
Si la fecha base, de la cual partes, estuviera en la celda A1, podrías obtener la fecha anterior restándole 1.
De este modo: = A1 - 1.
Para ir dos días hacia atrás, tendrías que restarle 2 (= A1 - 2).
Para ir 30 días para atrás, tendrías que restarle 30 (= A1 - 30).
Espero te sirva.
¡Saludos!
Hola. Te agradecería si me pudieras ayudar con lo siguiente. Debo hacer una aproximación de horas. Te doy un ejemplo:
de 6:00 a 6:15 que esto lo asuma como 6:00
de 6:16 a 6:30 que esto lo asuma como 6:30
de 6:31 a 6:45 que esto lo asuma como 6:30
de 6:46 a 7:00 que esto lo asuma como 7:00
Y así sucesivamente.
Hola, Alberto. ¿Cómo estás?
Para el resultado que necesitas podrías usar la siguiente fórmula.
(En el ejemplo, usé "A1" como referencia genérica. Sustitúyela por la referencia de la celda a evaluar.)
=(HORA(A1) + SI(MINUTO(A1)<=15; 0; SI(MINUTO(A1)<=45; 0,5; 1))) /24 Para ver el resultado con formato de hora tal vez tengas que aplicárselo manualmente. Espero te sirva. Saludos.
¡Buenas, ayuda!!!! Necesito algo que me solvente esto:
La fecha de pago de las cuotas de un préstamo es el día 30. ¿Qué variable puedo añadir para que luego de febrero no continúe con 28 o 29 y vuelva a los días 30?
Gracias.
Hola, José.
No sé exactamente cómo tienes que implementar el control.
Pero te puede ser útil la función FIN.MES, que devuelve correctamente el último día de mes, sin importar la cantidad de días que tenga.
La función FIN.MES lleva dos argumentos: la fecha de la cual queremos partir y la cantidad de meses hacia adelante o hacia atrás que queremos movernos para obtener el último día.
Por ejemplo, suponiendo que la celda A1 contuviera la fecha 10/6/2022:
=FIN.MES(A1; 0) da como resultado: 30/6/2022, porque es el último día del mes de la fecha indicada.
=FIN.MES(A1; 1) da: 31/7/2022, porque es el último día del mes siguiente al indicado.
=FIN.MES(A1; 4) da: 31/10/2022, porque es el último día del cuarto mes siguiente al indicado.
=FIN.MES(A1; -1) da: 31/4/2022, porque es el último día del mes anterior al indicado.
Espero te sirva.
Saludos.
Tengo una tabla dinámica con muchos años como base de datos, pero, de una semana para acá, cuando filtro me sale >12/12/2021, ¿cómo puedo resolver este problema y que se muestre la fecha real?
Hola, Diego.
Este es un tema relacionado más con la tabla dinámica que con los formatos de fecha.
No estoy seguro de entender del todo lo que consultas.
Pero pienso que puedes solucionar el problema haciendo clic con el botón derecho del ratón sobre una de las fechas y elegir la opción "Desagrupar" del menú contextual.
Otra opción podría ser elegir la opción "Agrupar" y cambiar la configuración de la ventana de acuerdo a tus necesidades.
Espero que la respuesta te sea útil.
¡Saludos!
¿No hay forma de romper con esa barrera del año 1900? Quiero poner fechas mas antiguas...
¿Hay alguna macro o algo que cambie esa configuración?
Hola, Gastón.
No hay una forma sencilla de hacer que Excel reconozca como válidas las fechas del 1/1/1900.
Pero el sitio oficial de Microsoft ofrece una solución, basada en programación VBA. Este es el enlace (es una página en inglés): https://docs.microsoft.com/en-us/office/troubleshoot/excel/calculate-age-before-1-1-1900
Veré si puedo estudiar un poco más el asunto y publicar un artículo en español al respecto.
¡Saludos!
Yo estoy sumando días y horas. Necesito el número de días consecutivos. Pero al intentar darle formato de celdas no me lo agrega. Ejemplo:
28, 11:37:00 (es decir, llevo 28 días sumados, pero requiero ver los demás, en la celda siguiente debo sumar)
04, 05:56:00 y solo me devuelve 01, 17:33 lo cual es un error, pues deberían ser:
33 (dias), 1:33
Sin embargo, no puedo sacarlo en las celdas, ¿me puedes apoyar? De verdad me ayudaría mucho.
Gracias.
Hola, Carolina.
Disculpa, pero solo con esos datos no puedo entender el problema.
Necesitaría ver la fórmula en su contexto.
Lo siento.
¿Cómo puedo hacer que las horas y minutos en cada celda vayan aumentando? Es decir, si tengo en una celda 5:10, en la siguiente celda quiero que aparezca 5:15... y así sucesivamente.
Hola, José.
Para eso podrías dar estos dos pasos:
1. Escribir las dos primeras fechas en las dos primeras celdas, en una 5:10, en la otra 5:15.
2. Seleccionar ambas celdas y arrastrarlas, como cuando arrastras una celda con fórmula.
Esto genera una serie de horas que sigue el patrón indicado en las dos primeras celdas.
Espero te sirva.
¡Saludos!
Muy interesante. gracias.
Gracias a ti, Teresa.
Me alegro de que haya resultado interesante la información.
¡Saludos!