¿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 (Google Drive) 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.
23 Response to "Horas entre dos fechas"
Me ha ahorrados horas de calculos aburridos .. muchisimas gracias!!
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.
excelente muchas gracias por su ayuda desinteresada.....
GRACIAS infinitas de verdad son unos genios
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.
Gracias por tu explicación, es clara y muy util
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.
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é
Muchisimas gracias. Gran aporte
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.
Hola Pedro, puedria enviarle un correo electrónico para una consulta de un archivo de excel que tengo, muchas gracias
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?
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
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.
Hola, Estoy queriendo calcular el tiempo real transcurrido en horas con dos decimales de días laborales, quiero decir menos sábados, domingos y días feriados y no encuentro una formula exacta de esta fechas:
12/28/2016 16:11 12/29/2017 10:52 = en horas (00,00)
2/17/2017 11:00 12/27/2017 12:21
4/11/2017 18:23 5/8/2017 8:34
3/21/2017 9:04 3/24/2017 17:05
Por favor agradeceré si alguien me podría ayudar con la formula
Sumar horas a una fecha y hora
En la celda A1 tengo una fecha con una hora (13-03-2018 10:30). Tengo una tareas de duran 110 horas, mi horario de trabajo es de 8:00 a 19:00. Y necesito saber la fecha en la que acabarían esas tareas, pero quitando las horas no laborables y los fines de semana.
Si me podéis ayudar, me salváis la vida, ya no sé por donde tirar con el excell
Hola, buenas tardes. Tengo la misma pregunta que hizo el pasado 13 de marzo el usuario "Unknown". Necesito calcular lo siguiente: En una celda con formato de fecha, hora y minutos capturo la "Fecha y hora de asignación" de una tarea. En otra celda tengo ya calculado el número de horas que se requieren para su cumplimiento; sin embargo, preciso de una tercer celda en donde, con formato de fecha, hora y minutos, me calcule la suma de las dos primeras celdas considerando únicamente días hábiles de lunes a viernes con una jornada laboral de 8 am a 4 pm. Llevo leyendo todo lo que he podido de excel al respecto desde hace tres días y no encuentro algo que me ayude. Muchas gracias de antemano.
Hola que tal! Necesito que Excel me calcule una hora a partir de restar 2 horas a la hora inicial. Cómo hago?
A1= 3pm
A2= 2 (horas)
A3= 1pm (este es el dato que necesito calcular pero no se como hacerlo...)
muchas gracias por compartirnos tu conocimiento y resultado. Me has ayudado mucho. Saludos.
hola muy interesante, pero tengo un problema requiero sumar 1 dia a una fecha dada en fecha y hora y me sale error, como puedo hacerlo ejemplo A1="21/11/2020 01:25:00 p. m." y a esto debo sumarle 1 día o 25 horas asi sucesimvamente
Para introducir una fecha sobran las comillas en A1.
Sumar un día: =A1+1
Sumar 25 horas: =A1+25/24
Hola buena noche. He buscado por muchos lugares y no he encontrado aun una solucion. Soy jefe de Enfermeria y veo que en donde trabajo, el calculo de cantidad de horas trabajadas por mis colaboradores siempre presenta fallas, porque aun hacemos el calculo "a mano". Estoy buscando la formula que me permita saber cuantas horas trabajo un colaborador, pero ademas que me arroje de manera automatica, la cantidad de horas nocturnas (que en Colombia son desde las 21:00 hasta las 6:00 del otro dia) y ademas necesito buscar como solucionar lo de descartar cuantas han sido festivas y/o dominicales. (Por ejemplo: un auxiliar empezó turno un domingo a las 14:00 y salió de turno el lunes a las 6:00) ahora sumen este problema por 32 auxiliares que tengo en el servicio de hospitalizacion.
Cesar Mosquera, este blog no es un foro de ayuda, por lo que deberías hacer tu consulta en un foro como: https://foro.todoexcel.com/
Te registras como usuario y creas un tema con tu problema, adjuntando una hoja Excel con datos de ejemplo y la solución que buscas.
Un saludo.
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.