Horas entre dos fechas

Posted on domingo, marzo 17, 2013 by Pedro Wave

¿En que empleo las horas entre fechas de cambio laboral?

En escribir este artículo, que es el número 100 de este blog, que pronto alcanzará las 200.000 páginas vistas, aunque realmente he escrito 50 artículos originales en dos versiones, española e inglesa.

¿Para qué sirve este artículo?

La plantilla en Excel que publico, y que se puede descargar más abajo, sirve para obtener el número de horas entre dos fechas cualesquiera, con hasta 4 rangos horarios diferentes y sin la condición de que las horas inicial y final estén dentro de los horarios de trabajo, permitiendo elegir qué días de la semana son laborables (fin de semana tradicional de sábado y domingo o solamente domingos o cualquier otro día).

Para un rango o turno horario se emplea este esquema para su cálculo que, extrapolándolo sirve para calcular diferentes rangos o turnos, simplemente sumándolos.



¿A quién le puede servir?

A los profesionales de Recursos Humanos, a los gestores de proyectos y presupuestos, a los gerentes que calculan los esfuerzos en horas-hombre u horas-persona, a los trabajadores a tiempo parcial, a los pluriempleados, a los maestros y profesores para organizar sus clases y, en general, a quien necesite saber cuántas horas dedica a la formación, al trabajo, al ocio, etc.

¿En qué me he inspirado?

Hace más de dos años yo estaba haciendo calendarios en Excel para este blog y mi amigo Chandoo lanzó un reto: "¿Cuántas horas trabajò Johnny?"

¿Cómo resolví el reto?

Me puse inmediatamente a calcular las horas trabajadas entre dos fechas por Johnny, sin contar sábados, domingos ni festivos y obtuve esta fórmula que tuvo mucho éxito por su simplicidad:

Siendo B3 la fecha y hora inicial y C3 la fecha y hora final, para 9 horas laborables.

Chandoo me felicitó por esta fórmula en su siguiente artículo "Cómo calcular las horas trabajadas entre dos fechas" y unos cuántos simpatizantes comenzamos a comentar nuestras fórmulas, entre ellos los gurús de Excel Daniel Ferry y Chip Pearson.

¿Cómo calcular una solución más general?

La fórmula anterior y las propuestas por Daniel Ferry, Michael, sexseven y Elias tenían como condición que tanto las horas de inicio y final están dentro del horario de trabajo y Chandoo nos volvió a retar para resolver un problema que podría ser aún más interesante, obtener las horas de trabajo entre dos fechas dadas sin la condición de que tanto la hora de inicio como la hora final están dentro de los horarios de trabajo.

Me puse a analizar y a recopilar todas las propuestas de mis compañeros del foro Chandoo.org y todo lo que encontré en la Web y, al no ver una solución inteligente, me puse a desarrollarla durante 5 días hasta llegar a esta nueva fórmula:

Siendo:
DecimalHours = $D$29 – $D$28 (número de horas de trabajo diario)
$D$28 = Hora inicial del trabajo
$D$29 = Hora final del trabajo

¿Qué me hace retomar de nuevo el cálculo de horas entre fechas?

Hace cuatro días jcascon11 planteó una consulta en el foro de AyudaExcel:

Calcular tiempo en horas entre dos fechas sin festivos ni fines de semana y con rango horario

Necesito calcular el tiempo entre una fecha inicial y una fecha final sin contar festivos, sábados y domingos y con este rango horario
De lunes a jueves el horario a contar es de 8:00 a 15:00 y de 17:00 a 19:30
Los viernes de 8:00 a 15:00
¿Alguien me puede ayudar?


¿Cómo calcular las horas con más de un rango horario de trabajo?

Puedes descargar el fichero con los cálculos según la versión de Excel:
- Columna D para Excel 2003 y 2007, que usa las columnas auxiliares E:K
- Columna C para Excel 2010 y posteriores, que usa la nueva función:
DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos])

Los valores de las cadenas de fin de semana tienen siete caracteres de longitud y cada carácter de la cadena representa un día de la semana, comenzando por el lunes. 1 representa un día no laborable y 0 representa un día laborable. Sólo se permiten los caracteres 1 y 0 en la cadena. 1111111 no es una cadena válida. Por ejemplo, 0000011 daría como resultado un fin de semana que es sábado y domingo.

En los dos casos empleo variantes de la fórmula que propuse hace más de dos años en el foro de Chandoo, aunque en Excel 2010 se puede hacer con una única megafórmula y con versiones anteriores de Excel hacen falta columnas auxiliares para hacer lo mismo.

¿Dónde descargo la plantilla?

Presionando en este enlace o en el botón de Descarga.

Descarga el fichero desde el icono (Sites Google) o desde el enlace (Microsoft OneDrive)


¿Por qué no lo pruebas en la nube tú mismo?

Esta es la plantilla interactiva para los que no tienen Excel 2010 y quieran ver el resultado del total de horas en la columna C con esta versión de Excel. Se pueden modificar todas las celdas con fondo amarillo.

Como Microsoft no contempla las validaciones de datos en Excel Web App, se debe introducir obligatoriamente un 0 (día laboral) o un 1 (día no laboral) en el rango E2 a K5.



¿Cómo he probado si el cálculo de las horas es correcto?

Introduciendo rangos de días diferentes, con la ayuda de estas dos fórmulas que se basan en la función ALEATORIO() incluidas en las celdas A25:B34 para la Fecha inicial (columna A) y Fecha final (columna B):
Los valores elegidos son 41275 para el 01-01-2013 y 41640 para el 01-01-2014.

En la columna L comparo las horas calculadas en Excel 2010 (columna C) y en otras versiones (columna D) con la siguiente fórmula:


¿Dónde seguir aprendiendo a calcular fechas y horas con Excel?

Aunque hay muchos sitios, como el blog de Chandoo para los que sepan inglés, no está mal comenzar por la web de preparación para opositores de José Enrique Martínez: Cálculos con fechas y horas en Excel

¿Continuaré escribiendo artículos en este blog?

Hasta ahora tengo que agradecer enormemente la buena acogida que está teniendo este blog entre los curiosos que se acercan a los interfaces gráficos de usuario y a los que buscan una ayuda para resolver sus cálculos en Excel.

En función del tiempo que tenga, en adelante haré lo posible por seguir aportando ideas y ejemplos de estos dos maravillosos mundos, tratando de que sirvan de ayuda a mis lectores.  Lo que si es seguro es que a mí me sirven para plantear y resolver problemas y para retarme e intentar experimentar y dominar un poco más cada día estas ondas de cálculo y programación.

13 Response to "Horas entre dos fechas"

.
gravatar
Anónimo Says....

Me ha ahorrados horas de calculos aburridos .. muchisimas gracias!!

.
gravatar
Pedro Wave Says....

Precisamente Excel sirve para ahorrarnos los cálculos aburridos, siempre que sepamos usar correctamente las fórmulas, porque si no se usan con cuidado pueden complicarnos la vida.

.
gravatar
Pedro Wave Says....

Hola Ronald y BARRY, me alegra saber que las horas que dediqué a este artículo le sirven a alguien más que a mí mismo, lo que me anima a seguir publicando más.
Saludos.

.
gravatar
Manuel Orozco Says....

Hola, estoy intentando crear en excel un cuadro de amortizacion, y no consigo hacer la fórmula correcta para obtener la respuesta adecuada de excel.
Osea,
1° en una celda introduzco la fecha de compra del objeto adquirido, ej 01/01/2014.
2° en otra celda pongo la fecha de fin de amortizacion del objeto adquirido, p.ej. 31/08/2024.
3° en otra celda introduzco la fecha actual (o cualquier otra)
Se trataría de cómo hacer que excel me diga si esta última fecha (la actual p.ej.) está comprendida entre la fecha de compra y la fecha de fin de amortización.
Si alguien me puede ayudar se lo agradeceré enormemente.
Muchas gracias de antemano.

.
gravatar
Sam Meza Says....

Hola, estoy tratando de hacer una tabla para calcular minutos durante el año en tres diferentes tiempo, primavera verano, otoño y invierno quiero una tabla de 15*24*365 (por cada 15 minutos del año) como puedo hacerlo, si alguien puede ayudarme lo agradeceré

.
gravatar
Carlos Bellido Says....

Hola, mi pregunta va referida a la fórmula:
=(DecimalHours*(DIAS.LAB(B3;C3;HolidayList)-1)+ SI(DIAS.LAB(C3;C3;HolidayList)=0;$D$29;MAX($D$28;MIN(RESIDUO(C3;1);$D$29)))- SI(DIAS.LAB(B3;B3;HolidayList)=0;$D$28;MIN($D$29;MAX(RESIDUO(B3;1);$D$28))))*24
Me gustaría que pudiera explicar la parte de las funciones condicionales SI, más concretamente los cálculos de MAX y MIN. Entiendo el cálculo de cada función por separado, pero no por qué se incluyen algunos cálculos.
Gracias de antemano.

.
gravatar
Manuel Hernando Ríos cifuentes Says....

Buenas tardes, Si tengo un numero de horas determinad, calculado entre fechas y quiero saber cuantas personas debo contratar, sabiendo que cada persona debe cumplir con un numero de horas laborales (Ej. 8hrs). Que formula debo aplicar?

.
gravatar
Sintia Mora Says....

Hola
por favor si me puedes ayudar con la siguiente situación:
Tengo una hoja de excel con un cronogramas de actividades para x cantidad de personas, es un archivo por persona. Hay una programación que se debe respetar, por ejemplo yo asigno a una actividad 60 horas, que inician el 1-3-2017 y por día se asignan 6.4 horas para esta actividad. Al campo fecha, le sumo 60 para ver la fecha de finalización, y divido 60/6.4 para saber cuántos días se requieren ejecutando la actividad, son 9.4 días; pero la fecha de finalización puede caer un sábado o domingo, me gustaría una fórmula que cuando detecte esto, me de la fecha de finalización en el lunes siguiente.
El otro tema es que para cada persona se registran vacaciones, reuniones y otras actividades, como puedo para hacer para que en esa misma hoja de excel cuando se registra la actividad anterior y por ejemplo hay un día de vacaciones registrado el 8-3-2017, que respete ese día y me sume la fecha de finalización de la tarea un día o x días según las fechas de vacaciones o feriados que ya están registradas en la misma plantilla de excel.
Es decir la actividad que inicia el 1-3-2017 finalizaría el 3 de mayo (porque el 8-3-2017 es un dia de vacaciones, el 1° mayo es feriado, más los 9.4 días que se lleva la actividad) debería finalizarlo de esa manera.

Mil gracias, saludos

.
gravatar
Pedro Wave Says....

Sintia, este tipo de consultas te las resolverán mis amigos de https://ayudaexcel.com/foro/ si te registras gratuitamente en ese foro y subes un archivo de ejemplo.
Un saludo.

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Tell me if you like what you read here and if you don't like, tell me why. I've enabled comment moderation. Your comment will be published ASAP.

Mi Lista de Blogs- My Blog List