Suma de horarios diurnos y nocturnos en excel
He creado una hoja de cálculo para poder sumar las horas trabajadas diariamente durante un mes, donde existen horarios de mañana, nocturnos u horarios partidos.
La hoja consta de:
- Columna A: Días del mes
- Columna B: Inicio de la jornada 1
- Columna C: Fin de la jornada 1
- Columna D: Inicio de la jornada 2
- Columna E: Fin de la jornada 2
- Columna F: Suma de horas diarias
Como los turnos son variados y a veces se deben sumar horarios nocturnos, para que la suma de horas diarias (Columna F) resulte bien, le he asignado la siguiente fórmula:
=SI(C<B;24-B+(C);C-B)+SI(E<D;24-D+(E);E-D)
Ejemplo:
B______C______D______E______F_____
14:00 - 18:00 / 20:00 - 01:00 = 09:00
Al aplicar esta fórmula =SI(C27<B27;24-B27+(C27);C27-B27)+SI(E27<D27;24-D27+(E27);E27-D27), en la suma de horas diarias (Columna F) me da como resultado 9 horas.
Ahora bien, al realizar la suma total de las horas diarias de todo el mes (Columna F), aun poniendo el formato de la celda en [hh]:mm, el resultado me da mal. En lugar de darme 89:30 horas/mensuales me da este número 6161:30 y no sé el porque. Si que he observado que si borro los horarios donde aparecen horas que pasan de las 23:59, la suma de los horarios restantes da bien, pero no he hallado la solución a mi problema.
Y ya puestos, tengo una segunda duda. He creado dos columnas para cuando existen cambios de horarios, en ellas se calcula la diferencia de tiempo de dicho cambio, y si se suma (Columna J) o se resta (Columna K) del horario original. Una vez sumadas las horas/mensuales ganadas y las horas/mensuales perdidas, intento sacar su diferencia con la siguiente fórmula:
=SI(K>J;K-J;J-K)
Ejemplo:
J______K_______F____
02:00 - 05:00 = 03:00
Al aplicar esta fórmula =SI(K34>J34;K34-J34;J34-K34), la diferencia de horas/mensuales ganadas o perdidas me da como resultado 3 horas.
El problema es que esas 3 horas deberían salir con signo negativo, para dar a entender que se han perdido, pero excel no da resultado negativo con las horas, con lo que he tenido que usar esa fórmula, pensando que al poner en ella "-" cuando el valor de K fuese mayor, me lo solucionaría, pero no. No sé como hacer para que me de el resultado negativo.
¿Alguien podría ayudarme?
1 respuesta
H o l a:
Resolviendo tu primer duda:
Te recomiendo lo siguiente:
En las celdas deberás capturar la fecha y la hora:
20/10/2015 02:00:00 p.m.
Se vería así:
En el turno 1 pones la fecha y hora de inicio y la fecha y hora de fin.
De igual forma en el turno 2 pones la fecha y la hora de inicio y la fecha y hora de fin.
En la columna K estoy poniendo la fórmula:
=(C16-B16)+(E16-D16)
La fórmula es más sencilla y el resultado me lo pone en horas y minutos.
De esa forma ya puedes sumar las horas de la columna K, el resultado le pones el formato [hh]:mm
Si lo prefieres, puedes capturar la fecha en una columna y la hora en otra:
La fórmula en la columna K:
=(D27+E27)-(B27+C27)+(H27+I27)-(F27+G27)
Para la segunda duda, si quieres que te ponga el signo, podría ser así:
=SI(K34>J34;"-" & TEXTO(K34-J34;"[hh]:mm");J34-K34)
Pero el resultado sería un texto, es decir, no podrías ocuparlo como un valor numérico para realizar operaciones.
Otra recomendación sería que tuvieras en una columna el resultado positivo y en otra el resultado negativo:
Por ejemplo en la columna L los positivos:
=SI(K34>J34;"";J34-K34)
En la columna M los negativos, el resultado será 3, pero ya sabes que en la columna M tienes los negativos:
=SI(K34>J34;K34-J34;"")
