GUÍA DE HOJAS DE CÁLCULO

Cómo calcular horas trabajadas en Excel con turnos de noche

En un turno de 22:00 a 06:00, la resta normal da un número negativo y Excel muestra ####. La fórmula =RESIDUO(salida-entrada;1) suma un día cuando la salida es anterior a la entrada y devuelve las 8 horas del turno.

Responsable: Numalto · Actualizado el

Por qué falla la resta en un turno de noche

Las horas de reloj se guardan como fracciones de un día: 22:00 es 0,9167 y 06:00 es 0,25. Si la salida está en C2 y la entrada en B2, =C2-B2 da −0,6667, es decir, −16 horas. Excel no sabe que la salida es del día siguiente y, como no muestra horas negativas, rellena la celda con almohadillas.

La fórmula: RESIDUO(salida-entrada;1)

Para obtener Configuración española (;) Configuración inglesa (,)
Horas del tramo, aunque pase de medianoche =RESIDUO(C2-B2;1) =MOD(C2-B2,1)

RESIDUO con divisor 1 se queda con la parte fraccionaria y siempre devuelve un valor entre 0 y 1: a −0,6667 le suma un día y queda 0,3333, que son 8 horas. Si la salida es posterior a la entrada, el resultado no cambia. Da al resultado el formato [h]:mm.

Ten en cuenta sus límites:

  • Supone el día siguiente sin preguntar. Si escribes 06:00 cuando querías 16:00, la fórmula lo convierte en un turno de noche sin avisar. Revisa las horas que anotas.
  • Entrada igual a salida da 0:00, no 24 horas. La fórmula no distingue un turno de 24 horas de uno vacío.
  • No admite tramos de más de 24 horas. Para eso necesitas fecha y hora en cada extremo: =(D2+C2)-(A2+B2), con la fecha de entrada en A2 y la de salida en D2.

Descontar las pausas

Si anotas la pausa en minutos en D2, divídela entre 1440, los minutos de un día, para restarla en la misma unidad que las horas.

Para obtener Configuración española (;) Configuración inglesa (,)
Horas netas del tramo =RESIDUO(C2-B2;1)-D2/1440 =MOD(C2-B2,1)-D2/1440
Horas netas en decimal =(RESIDUO(C2-B2;1)-D2/1440)*24 =(MOD(C2-B2,1)-D2/1440)*24

Si prefieres escribir la pausa como hora, por ejemplo 0:45, réstala sin dividir: =RESIDUO(C2-B2;1)-D2. No escribas 45 en una celda de hora: Excel lo entendería como 45 días.

Ejemplo: una semana de turnos de noche

Cada tramo se atribuye al día en que empieza. Con la entrada en B, la salida en C, la pausa en minutos en D y la fórmula de horas netas en E:

Día Entrada Salida Pausa (min) Horas netas Decimal
Lunes 22:00 06:00 45 7:15 7,25
Martes 22:00 06:00 30 7:30 7,50
Miércoles 23:00 07:30 30 8:00 8,00
Jueves 21:30 05:00 30 7:00 7,00
Viernes 22:00 06:30 45 7:45 7,75
Total 37:30 37,50

Total semanal y comparación con la jornada pactada

Suma la columna de horas netas con =SUMA(E2:E8) (=SUM(E2:E8)) y da al total el formato [h]:mm, para que no vuelva a cero al pasar de 24 horas. Si escribes tu jornada semanal pactada en H2, por ejemplo 40:00, puedes compararla con el total de E9.

Concepto Horas
Total trabajado 37:30
Jornada semanal pactada 40:00
Diferencia sobre tu jornada −2:30 (defecto)

La resta =E9-H2 es negativa cuando trabajas menos de lo pactado, y Excel la mostraría como ####. Para verla con su signo:

Para obtener Configuración española (;) Configuración inglesa (,)
Diferencia con signo, como texto =SI(E9<H2;"-";"+")&TEXTO(ABS(E9-H2);"[h]:mm") =IF(E9<H2,"-","+")&TEXT(ABS(E9-H2),"[h]:mm")
Diferencia en horas decimales =(E9-H2)*24 =(E9-H2)*24

La diferencia solo compara las horas anotadas con la jornada que indicas. No equivale a horas extraordinarias: qué cuenta como tal depende de tu contrato y de tu convenio.

Errores frecuentes

  • Escribir 24:00 como hora de salida. Excel lo guarda como un día completo y lo muestra como 00:00. Escribe 00:00: con RESIDUO, de 22:00 a 00:00 salen 2:00.
  • Restar la pausa en minutos sin dividir entre 1440. =RESIDUO(C2-B2;1)-D2 con 45 en D2 resta 45 días.
  • Olvidar el formato [h]:mm en el total. Una semana de 37:30 se vería como 13:30.
  • Esperar que la fórmula corrija el cambio de hora. RESIDUO calcula horas de reloj: la noche del cambio de hora el turno dura una hora más o menos. Para esa noche, usa la calculadora del cambio de hora.

Plantilla para descargar

Las fórmulas de esta guía, listas para usar: una hoja de instrucciones y otra de cálculo con las fórmulas protegidas contra borrados accidentales, sin contraseña. Archivo .xlsx sin macros para Excel o Google Sheets.