Calcular numero de horas entre fechas en formato dia-mes-año-hora, descontando no laborables

[oscarcarrasco10]

Hola,

Llevo dias intentando resolver la siguiente situacion, pero no doy con la combinacion de formulas en excel2007.

He buscado en el foro algo similar y aunque, hay temas parecidos, no he sido capaz de encontrar una solucion que me ayudase.

La formula/s que necesito desarrollar, calcularia el tiempo transcurrido (en horas y minutos) entre dos fechas con celdas en formato dia-mes-año-hora (ver ejemplo adjunto).

El problema empieza ahora, porque pero necesito descontar lo siguiente:

- La jornada laboral es: L-V (05:00 am a 03:00 am del siguiente dia, Total 22 h/dia)

- Fines de semana, si los hubiese.

- Festivos, si los hubiese.

El ejemplo adjunto, trata de calcular lo siguiente:

A. El tiempo transcurrido (H:mm) en fabricar un lote, Tiempo de Ciclo A (Fecha Hora Fin Lote 1- Fecha Hora Inicio Lote 1)

B. El tiempo transcurrido (H:mm) en limpieza entre lotes B (Fecha Hora Inicio Lote 2 - Fecha Hora Fin Lote 1)

El problema me surje para descontar las dos horas al dia que no se trabaja y los fines de semana o festivos si los hubiese entre las dos fechas.

Espero que alguien me pueda ayudar. Muchas gracias por la ayuda.

Ejemplo.rar

[jrgces]

Mira si en el libro adjunto los cálculos son los que deberían ser. Yo he calculado a mano E3 y F4 y me sale lo mismo que lo que devuelven las fórmulas, pero podría suceder que no hubiese entendido bien las premisas, con lo que los cálculos estarían dando resultados incorrectos.

Saludos.

20121030b.zip

[oscarcarrasco10]

Hola,

En primer lugar muchas gracias por la ayuda.

He probado la formula y parece que funciona en la mayoria de los casos. Solo he encontrado un gap al calcular tiempos muy cortos entre fecha-hora cerca del final de un dia y fecha-hora al inicio del dia siguiente, ya que sigue descontando las 2 h.

No se si sera posible solucionarlo, pues obviamente excel no sabe si la jornada (de 22 h/dia) acaba a las 22 h o a las 3 h del dia siguiente, como es el caso.

Mira el mismo ejemplo que envie en el que he modificado horas para ver que la formula funcionaba, para que puedas entender lo que quiero decir. De todas formas creo que podria utilizar la formula, puesta esta situacion muy rara vez se da.

Entiendo basicamente la idea de la formula, pero hay un par de cosas que no llego a entender y que si me las pudieses explicar te lo agradeceria. Es lo siguiente:

- El +1 despues de calcular el numero de dias laborables entre las fechas.

- La resta: 2/24 y 2*(2/24) que entiendo que es para descontar las 2 h que no se trabajan en un dia, pero no estoy seguro de la logica.

Muchas gracias por anticipado a tu respuesta.

20121030ba.rar

[jrgces]

El +1 es para poder calcular el número de horas trabajadas el día inicial. Por ejemplo, si la hora de inicio son las 18:00 habría que restar a 24 horas las 18 que han transcurrido del día inicial, y como 24 horas = 1 día lo más fácil es utilizar directamente ese 1.

El 2*(2/24) está también relacionado con este 1. Como estamos trabajando siempre con días de 22 horas, no se puede utilizar directamente 1 sino 1-2*(2/24). Para más claridad debería haber puesto ese 2*(2/24) justo después del 1 en lugar de al final de la fórmula, pero ni lo pensé.

El -(2/24) es para descontar las dos horas no laborables del día final. Por ejemplo, si la hora final es las 18:00, las horas laborables de ese día no habrán sido 18 sino 16, puesto que el intervalo 00:00:00 a 02:00:00 no es laborable. Lógicamente serviría también -(1/12), o incluso directamente 0,08333333333 pero me pareció un poco menos "oscuro" dejar 2/24.

El error de 2 horas que se produce en F6 pienso que se debe precisamente a este cálculo, ya que al escribir la fórmula asumí que la hora final nunca estaría en el intervalo 00:00:00 a 02:00:00 porque supuse que eran inhábiles. Por eso si en vez de 26/10/2012 00:00 fuese 25/10/2012 23:59:59 la fórmula devolvería el resultado correcto, 4:59:59

Este error se puede evitar complicando un poco la fórmula añadiendo un SI:

=(DIAS.LAB(D5;C6;$K$6)*(22/24))+1-(D5-ENTERO(D5))+C6-ENTERO(C6)-SI(HORA(C6)>2/24;2/24)-(2*(22/24))[/CODE]

Otra posibilidad sería considerar "prohibido" ese intervalo de dos horas (repito, desde las 00:00:00 hasta las 02:00:00), y sumar dos horas a cualquiera que pueda haber en ese intervalo, o sea que 26/10/2012 00:00 pasase a ser 26/10/2012 02:00

Saludos.