Si utilizas Excel de forma habitual, seguro que te has cruzado en alguna situación con fechas y horas. Excel trata estos tipos de datos como si fuesen números, por lo que hacer operaciones con ellos resulta tremendamente fácil.
Hoy te voy a mostrar cómo funcionan las fechas y horas en Excel y te enseñaré a hacer algunos cálculos fáciles con este tipo de datos.
Comenzamos…
Configuración regional
Antes de meterme de lleno en el tema he de advertirte de que puede que la configuración regional de tu equipo sea diferente al del mío. En mi ordenador tengo la configuración de fechas dd/mm/aaaa, así que en todos los ejemplos en los que se muestren fechas, aparecerá este formato. Es posible que, si tienes el formato de fecha anglosajón, la configuración de tu equipo sea mm/dd/aaaa. No le des importancia a esto, los ejemplos funcionan igual con una o con otra configuración.
Si quieres averiguar qué tipo de formato de fechas tienes configurado en tu equipo, simplemente accede al cuadro de diálogo Formato de número (pulsando Ctrl + 1) y selecciona la categoría Fecha. Las fechas marcadas con un asterisco (*) están basadas en el formato de tu equipo, así que este formato es el que debes utilizar cuando trabajes con fechas. Fíjate en el recuadro rojo:
Venga, vamos allá…
Las fechas y las horas en Excel
Ya lo he comentado muchas veces en la web pero es importante que entiendas esto:
Las fechas y horas en Excel son almacenadas como números (aunque se muestren con formato de fecha). Este valor es el “número de serie”. Para ver el número de serie de una determinada fecha, selecciona la celda, pulsa Ctrl + 1 y haz clic en la categoría General.
La numeración de fechas en Excel comienza el 01/01/1900, fecha a la que le corresponde el número 1. El 02/01/1900 tiene el número 2, y así sucesivamente. La última fecha para Excel es el 31/12/9999.
Si eres un poco geek o friki de los números te gustará saber que Excel considera el año 1900 como bisiesto, cuando no lo es. Simplemente Microsoft lo dejó así para que Excel siguiese teniendo compatibilidad con Lotus 1-2-3, que es la aplicación que realmente contiene el error.
Las fechas en Excel
Como te decía, Excel le da un número correlativo a cada día desde el 01/01/1900. A este número se le conoce como “número de serie”. Esta nomenclatura permite que se puedan hacer cálculos de cualquier tipo de complejidad.
Observa la siguiente tabla. La columna de la izquierda muestra una fecha y la columna de la derecha, su número de serie correspondiente.
Por ejemplo, el 1/12/18 es el número 43.435 porque han pasado 43.435 días desde el 1 de enero de 1900.
Las horas en Excel
Las horas funcionan de manera similar a las fechas. Una determinada hora está representada por un número decimal comprendido entre 0 y 1 donde 0 sería la medianoche del día y 1 la medianoche del día siguiente.
Digamos que cada 24 horas habría que sumar 1 al número de serie. Si, por ejemplo quisieras representar el mediodía, lo tendrías que hacer con el número 0,5 porque ha pasado medio día desde las 0:00 h. Cada hora que pasa habría que sumar 0,41666 (0,41666*24 h.=1).
Y si una hora es 1/24 de un día, un minuto es 1/60 de una hora (1/1440 del día), por lo que el número decimal que representa la hora puede ser tan preciso como necesites (incluso llega a milisegundos).
Te lo muestro en la siguiente tabla:
Fechas y horas juntas
Una vez que hemos visto que tanto las fechas como las horas pueden representarse con un número entero y decimal respectivamente, no te costará mucho imaginar que es posible darle un número a cualquier instante.
Por ejemplo, el día 28/05/18 a las 10:12 de la mañana tiene el número de serie 43.248,425. 43.248 corresponde al día y la parte decimal 0,425 corresponde a la hora.
Mira más ejemplos:
Introducir fechas y horas en Excel
Fechas
A la hora de introducir una fecha, Excel es capaz de reconocer varios formatos diferentes, que convierte en un número de serie y le da aspecto de fecha cuando pulsas Intro. Te muestro algunos ejemplos de datos que puedes introducir. Observa lo que ocurre con cada uno de ellos al pulsar Intro:
| Dato introducido | Fecha reconocida |
|---|---|
| 1-1-2018 | 01/01/2018 |
| 1-1-18 | 01/01/2018 |
| 1/1/2018 | 01/01/2018 |
| 1/1/18 | 01/01/18 |
| 1-ene-18 | 01-ene-18 |
| 1-ene 18 | 01-ene-18 |
| 1-ene-2018 | 01-ene-18 |
| 1 ene 2018 | 01-ene-18 |
| 1/1 | 1-ene |
Fechas con años de dos dígitos
Un problema muy común al introducir fechas cuyo año tiene dos cifras es el de elegir a qué siglo corresponde. Por ejemplo, cuando introduces la fecha 1/1/09, ¿qué año quieres introducir? ¿1909 ó 2009?
No te preocupes, tiene fácil solución. Cuando los dígitos del año son un número menor o igual que 29, el número se convierte en un año del siglo XXI. En cambio, si el número es mayor o igual que 30, significará que estás introduciendo un año del siglo XX.
Observa la siguiente tabla:
Para que no haya lugar a dudas, en caso de que quieras introducir un año diferente al actual, hazlo en formato de cuatro dígitos. En caso de querer introducir una fecha del año actual, ni siquiera hace falta que lo teclees.
Horas
Las horas tienen un formato un poco más estricto que las fechas. Como mínimo, para que Excel reconozca la hora, debes introducir al menos los valores h:mm (horas y minutos separados por dos puntos, sin espacios).
En caso de que quieras hacer referencia también a los segundos el formato deberá ser h:mm:ss.
Excel también admite las siglas AM/PM para hacer referencia a la mañana o la tarde. Simplemente introduce AM o PM después de la hora. También puedes aplicar este formato a la celda después de haber introducido el valor.
Mira estos ejemplos:
Introducir fechas y horas en Excel juntas
Ahora que ya sabes introducir fecha y horas por separado, es el momento de hacerlo conjuntamente en la misma celda.
Fíjate que puedes introducir primero la fecha y luego la hora y viceversa.
Algunos cálculos con fechas y horas en Excel
Ahora que has entendido cómo se almacenan las fechas y las horas en Excel, es hora de pasar a cosas más complejas. Como te comenté antes, los números de serie hacen posible realizar operaciones matemáticas con fechas y horas. A continuación, te muestro algunos ejemplos sencillos
Sumar o restar días de una fecha determinada
Como puedes ver en la tabla anterior, puedes agregar o quitar un número de días a una fecha determinada directamente en la fórmula. Se trata de una suma normal y corriente.
Restar fechas
Para averiguar los días de diferencia que hay entre dos fechas, lo mejor es hacer una resta. Mira la tabla.
Es posible que el formato de la celda donde introduces la fórmula no sea el adecuado (normalmente cuando haces operaciones con dos fechas, el resultado también es una fecha). Si quieres ver el número de días, cambia el formato a General o a Número.
Como en estos ejemplos no estoy utilizando horas, las fechas comienzan a contar desde las 00:00 del primer día hasta las 23:59:59 del último día.
Sumar tiempo a una hora
Observa en la siguiente tabla que no se muestra ninguna hora negativa. Excel no puede mostrar fechas negativas por lo que el resultado que se obtendría sería ####################.
También puedes ver en la tabla que las horas que exceden de un día natural están representadas por un número de serie superior a 1. Como la celda tiene formato de hora, no se muestra la diferencia real de horas, sino el resultado de sumar cuatro horas a las 10 de la noche.
Si deseas que se muestre la diferencia real (es decir, 26 horas), debes cambiar el formato de la celda a uno parecido a este: [h]:mm:ss.
Restar tiempo de una hora
De la misma forma que puedes sumar tiempo a una hora, también puedes restarlo. El resultado también será una hora. Observa cómo se muestra el resultado negativo en la siguiente tabla:
Restar una hora de otra
Si en vez de restar tiempo de una hora, lo que quieres es restar dos horas, la operación es la misma que la anterior: sólo cambian los datos que se van a restar.
Curiosidades sobre fechas y horas en Excel
- Excel no reconoce fechas anteriores a 01 de enero de 1900.
- Se mostrará en la celda ############# cuando contenga una fecha negativa.
- Excel no puede almacenar horas sin una fecha asociada. Cuando introduces en una celda, por ejemplo 17:30:00, se considera como las cinco y media del día 0 de enero de 1900
Modos de fechas
Excel tiene en estos momentos dos formas de calcular las fechas. El modo que te he mostrado en artículo se denomina “1900” porque los días comienzan a contar en ese año. Existe otro modo de calcular los días llamado “1904” que, aunque no se utiliza habitualmente, se sigue manteniendo por compatibilidad con versiones de Excel 2008 y anteriores para Mac.
Puedes cambiar en cualquier momento el modo de cálculo de las fechas dentro de las opciones avanzadas del cuadro Opciones de Excel, pero no te lo recomiendo a no ser que sea realmente necesario.
La diferencia entre los dos métodos de fechas está en que el método “1900” está 1.462 días por delante del “1904”, lo que podría ocasionar errores en algunos cálculos.
Ejemplos prácticos con fechas y horas en Excel
- Cómo calcular las horas de trabajo entre dos fechas
- Cómo extraer datos de las celdas que contienen fechas y horas
- Unir textos con fechas
- Cómo calcular las horas de trabajo entre dos fechas con jornadas diferentes
- Guía de uso de fechas y horas
- Cómo convertir los timestamps de Unix a fechas en un instante
- Hallar la diferencia en meses entre dos fechas
- Página oficial sobre ayuda en fechas y horas
Resumen
Las fechas y las horas son un tipo especial de número con los que puedes hacer operaciones matemáticas. Como has visto, tienen mucho que aprender y te recomiendo que lo hagas para que te resulte más fácil trabajar con fechas.
¿Has aprendido algo que no sabías con este artículo? Escribe un comentario.
21 comentarios en “Cálculos con fechas y horas en Excel”
Don Sergio hoy 11-05-18 he encontrado su página y me ha funcionado muy bien para calcular días en nombramientos de docentes que se dan por ejemplo del 09/07/2018 al 10/12/2018 este cálculo aplicado a horas en una jornada de tiempo completo calcula 8 horas más de la realidad.
1/4 de tiempo 304 con el cálculo 306. solo en razón de aclarar por que? se presenta ese.
muchas gracias genial esta página
Juan Carlos, eso puede ser debido a que determinadas funciones toman tanto la primera fecha como la última para calcular la diferencia..
Dime qué cálculo intentas hacer exactamente para poder echarte una mano.
Hola Sergio, muchas gracias por este aporte, tengo una duda, si me puede ayudar, dentro un calculo que realizo en excel obtengo 3,65 días, que serian 3 días y 12 horas y 30 minutos, mi consulta es si existe una forma de expresar este resultado así:
3,12:30:00
saludos
Hola, Ivan. ¿Qué cálculo haces para decir que 0,65 corresponde a 12 horas y media? Si le das formato de hora, te indica que corresponde a las 15:36.
Hola Sergio, muy buena la página. Te hago una consulta, porque no logro sacarlo. En una columna tengo fechas en formato dd/mm/aaaa. No sólo quiero extraer el año, sino que establezca un criterio para calcularlo. Es decir, lo que yo quiero saber es a qué ciclo lectivo corresponden: en este caso, el ‘año real’ sería desde el 1/3 al 30/3 del siguiente año. Muchas gracias.
Creo que la solución puede ser acotar en una tabla aparte las fechas de inicio y fin de cada año académico y hacer tu cálculo con un par de condiciones SI o con una animación de ÍNDICE y COINCIDIR. No obstante, los años de trece meses pueden resultar demasiado largos… 😉
Me interesa manejar correctamente los calculos para Nominas en ya que se aplican diferentes tasas a derentes Jornadas laboradas
Me interesan los Calculos en las diferentes Jornadas
Hola,
Tengo una consulta, quiero comprobar las horas trabajadas pero he de restar una hora para comer. Tengo la hora de entrada (A4) y de salida (A3) y en A5 he creado la fórmula =A3-A4, y ya tengo las horas trabajadas.
Ahora resto una hora =(A3-A4)-1:00:00, y me da ERROR.
No quiero vincularlo a otra celda, ¿como debo proceder?
Gracias.
Hola, Neli!
La forma más sencilla sería =(A3-A4)-(1/24). Con esto le restas una hora de las 24 que tiene el día.
Espero que te sirva.
Hola Sergio,
para saber cuantos dias y horas. Que formula puedo utilizar, por favor, me ayudas.
ejemplo.
16/06/2019 22:00 p.m.
18/06/2019 09:00 a.m.
?
Hola Adan!
Yo lo haría restando una fecha a otra y al resultado le pondría el formato dd hh.
me puedes apoyar quiero hacer un contar si conjunto donde mi rango a contar es un grupo de horas 10:00 11:00 etc. y quiero que cuente todas las que sean menores a las 13:00 pero al hacerlo siempre me cuenta cero
Héctor, prueba a transformar el formato de hora al formato de número. El número 0,54 equivale a las 13:00.
Se pueden calcular los días con una fecha inicial y la fecha del ordenador.
Gracias por su respuesta
Hola Julián!
La hora del ordenador la obtienes con la función =HOY(). Solo tendrás que restar la fecha que tengas con la de la función.
Muchas gracias Sergio! Estaba teniendo problemas con el calculo de horas, porque necesitaba la sumatoria en vez de la hora del dia. Se soluciono con el formato [hh]:mm “hs”
Hola Sergio, muy interesa y útil tu pagina. Tengo un caso de conversión de valor numérico a fecha y hora. Los datos los obtengo en este formato: 43748.3541666666. Para pasarlo a fecha y hora lo que hago es que trunco la parte entera y la copio en una celda y en otra celda los decimales. Luego convierto la parte entera a formato Día-Mes- Año y los decimales a Hora-minuto-segundo. ¿Hay algún modo de hacerlo sin necesidad de hacer esa operación y que quede de una vez en una sola celda?
Te consulto ya que cuando hago esto tengo que procesar una gran cantidad de datos y me quita tiempo hacerlo de este modo.
Gracias de antemano por tu ayuda.
Saludos.
Hola Teddy!
Sitúate en la celda que contenga el número y presiona Ctrl + 1. Ve a la ficha Número y, desde la categoría Personalizado, escribe la siguiente máscara: dd/mm/aaaa HH:mm:ss
Buenas tardes. Tengo una hoja que introducidas una fecha y una hora, calcula una hora de finalizacion de maniobra en funcion de otro valor X. Mi problema es que dada una hora (p ej. 20:00) al rellenar las horas de la columna hacia abajo, cuando pasamos de las 23:00 a las 0:00, Excel añade en el valor decimal de la hora un entero, lo que hace que la hoja sume 1 dia mas a la finalizacion de la maniobra, lo que me trastoca los calculos de sobremanera. Espero que pueda ayudarme. Saludos y gracias
Hola tocayo!
Introduce la siguiente máscara en el cuadro Formato de número (Ctrl + 1, pestaña Personalizada):
[h]:mm
Eso hará que al pasar las 12 de la noche, no se muestre en otra unidad.
Los comentarios están cerrados.