Utilizando MOD con valores de tiempo para determinar las diferencias de tiempo que atraviesan la medianoche.
Creé una hoja de cálculo de Excel con un Tiempo Total para la Duración del Turno, es decir, de 8:30 a 17:30.
Luego creé esta fórmula:
=(RESIDUO([@[Fin del Turno Previsto]]-[@[Inicio del Turno Previsto]],1))*24
con “Formato de celdas” -> “Número” con 2 decimales. Esto me da las [Horas Totales del Turno Previsto].
Esto me da la Duración del Turno que debe ser cubierto.
También creé otra columna para el tiempo REAL que el turno cubrió:
=(RESIDUO([@[Fin del Tiempo Real]]-[@[Inicio del Tiempo Real]],1))*24
Esto me da las [Horas Totales del Tiempo Real].
Para el Fin del Tiempo Real y el Inicio del Tiempo Real, cuando el empleado no se presentó, ingresamos 0 en ambas celdas con el mismo formato de celda “Número” con 2 decimales.
El problema:
Esta es la fórmula que escribí para restar estas 2 columnas:
=(RESIDUO([@[Horas Totales del Turno Previsto]]-[@[Horas Totales del Tiempo Real]],1))*24
¿Por qué cuando resto, por ejemplo, [Horas Totales del Turno Previsto] 9.00 – [Horas Totales del Tiempo Real] 0.00 = 24.00 ?
9 – 0 = 9, no 24…. suspiro
¿Se afectará la fórmula si el tiempo va de la noche anterior, de las 21:00 a las 8:00, utilizando la fórmula RESIDUO?
Datos de ejemplo (según lo solicitado):
Nota: La nomenclatura difiere de la descripción anterior: “Open” = “Supposed” y “Covered” = “Actual”
| Fecha del Servicio | Inicio del Turno Previsto | Fin del Turno Previsto | Horas Faltantes Totales | Inicio del Tiempo Real | Fin del Tiempo Real | Horas Totales Cubiertas | Categoría | Horas No Cubiertas |
|---|---|---|---|---|---|---|---|---|
| 02/06/2021 | 16:00 | 00:00 | 8 | 16:00 | 03:00 | 11 | A | 0 |
| 04/06/2021 | 16:00 | 00:00 | 8 | 00:00 | 00:00 | 0 | A | 0 |
| 10/21/2021 | 10:30 | 00:00 | 13.5 | 18:00 | 19:30 | 1.5 | B | 0 |
davy.ai
El problema en la fórmula
(MOD([@[Total Supposed Shift Hours]]-[@[Total Actual Time Hours]],1))*24es que cuando el tiempo real es 0, el resultado de la resta dará como resultado un número negativo, y cuando se aplica la función MOD, convertirá el número negativo en un número positivo, produciendo así el resultado inesperado.Para manejar esta situación, se puede agregar una declaración IF para verificar si el tiempo real es 0, y en caso afirmativo, devolver las horas de turno supuestas; de lo contrario, restar el tiempo real de las horas de turno supuestas.
La fórmula modificada sería:
=SI([@[Total Actual Time Hours]]=0,[@[Total Supposed Shift Hours]],(MOD([@[Total Supposed Shift Hours]]-[@[Total Actual Time Hours]],1))*24)En cuanto a la pregunta sobre el tiempo que va de la noche anterior al día siguiente, la función MOD lo manejará correctamente, ya que calcula el resto después de la división, por lo que considerará el número correcto de horas entre dos valores de fecha y hora, independientemente de la fecha.