Cómo llevar el control de horas y horas extra en Excel
Diseña una planilla ordenada para registrar marcas diarias, resolver turnos que cruzan la medianoche sin errores negativos y consolidar totales mensuales por trabajador.
Una planilla de asistencia mensual que calcula horas trabajadas (incluso en turnos nocturnos), separa jornada ordinaria de horas extra y resume totales por persona sin perder horas al superar las 24.
- Configurar el formato personalizado [h]:mm para sumar más de 24 horas.
- Calcular duración de turnos que pasan la medianoche con RESIDUO.
- Separar horas normales, horas extra y atrasos frente a la jornada pactada.
- Resumir horas mensuales por trabajador con SUMAR.SI.CONJUNTO.
Progreso: 0 / 6 pasos
0%Ruta paso a paso
6 pasos- 01
Formato [h]:mm
Evita que Excel reinicie los totales al pasar de 24 horas.
- 02
Registro diario
Arma una tabla estructurada con fechas, turnos y colación.
- 03
Turnos nocturnos
Usa RESIDUO para calcular salidas después de las 00:00.
- 04
Horas extra
Compara lo trabajado contra la jornada diaria pactada.
- 05
Resumen mensual
Consolida por trabajador y mes con SUMAR.SI.CONJUNTO.
- 06
Marco normativo
Buenas prácticas de respaldo y consulta en la Dirección del Trabajo.
Lo que necesitas
PrerrequisitosVersión recomendada: Excel 2016 o superior, Microsoft 365 o Excel para la Web
- Microsoft Excel instalado en Windows/Mac o acceso a Excel en la Web.
- Listado de trabajadores con su jornada diaria pactada (por ejemplo, 08:00 o 09:00).
- Registro de marcas de entrada, salida y tiempo de colación en formato de hora (HH:MM).
Mac y Web: Todas las fórmulas usadas (RESIDUO, MAX, MIN, SUMAR.SI.CONJUNTO) y el formato personalizado [h]:mm funcionan de forma idéntica en Excel para Windows, macOS y Excel para la Web.
1. Entender el formato de horas en Excel ([h]:mm vs hh:mm)
Antes de escribir una sola fórmula, conviene entender cómo guarda Excel el tiempo. Para Excel, un día completo equivale al número 1, doce horas equivalen a 0,5 y una hora equivale a 1/24 (aproximadamente 0,041667). Cuando escribes 08:30 en una celda, Excel en realidad almacena una fracción decimal y la muestra con aspecto de reloj.
El problema clásico aparece al sumar la semana o el mes de un trabajador. Si sumas cinco jornadas de 08:00, el valor interno es 40/24 = 1,6667 días. Si la celda tiene el formato estándar hh:mm, Excel interpreta el entero (1) como un día calendario completo y muestra únicamente la fracción restante: 16:00. A simple vista parece que la persona trabajó 16 horas en toda la semana, cuando en realidad trabajó 40.
Para corregir esto sin cambiar el valor numérico subyacente, debes aplicar el formato personalizado [h]:mm. Los corchetes alrededor de la letra h le indican a Excel que muestre las horas acumuladas totales, sin reiniciar el reloj cada 24 horas. Este detalle es crítico en cualquier planilla de control de horas en Excel, especialmente en las filas de totales y en los resúmenes mensuales.
- Selecciona las celdas donde calcularás totales semanales o mensuales.
- Presiona Ctrl + 1 (en Mac, Cmd + 1) para abrir Formato de celdas.
- En la pestaña Número, elige la categoría Personalizada.
- En el cuadro Tipo, escribe exactamente [h]:mm (o [h]:mm:ss si registras segundos) y acepta.
| Formato | Cómo muestra 40 horas | Cómo muestra 08:30 | Cuándo usarlo |
|---|---|---|---|
| hh:mm | 16:00 (se reinicia cada 24h) | 08:30 | Solo para marcas puntuales de reloj (entrada o salida). |
| [h]:mm | 40:00 | 08:30 | Obligatorio en duración de turnos, horas extra y totales acumulados. |
| Decimal (multiplicado por 24) | 40,00 | 8,50 | Útil si necesitas multiplicar horas por un valor monetario por hora. |
| Texto ('08:30) | No suma (da 0 o #¡VALOR!) | 08:30 | Evitar siempre; impide calcular diferencias y totales. |
2. Armar la tabla de registro diario sin celdas combinadas
Muchas planillas de asistencia fallan porque intentan imitar un calendario impreso con los nombres en filas y los días repartidos en treinta columnas hacia la derecha. Ese diseño dificulta filtrar por trabajador, complica los turnos nocturnos y vuelve frágiles los resúmenes mensuales.
La estructura recomendada es una tabla tabular o «larga»: cada fila representa una jornada de un trabajador en una fecha específica. Convierte el rango en una Tabla oficial de Excel con Ctrl + T (le puedes poner el nombre tblAsistencia en la pestaña Diseño de tabla). Al trabajar con una Tabla, las fórmulas se copian solas hacia abajo cada vez que agregas un nuevo día.
Incluye columnas claras y atómicas: Fecha, Mes (calculado automáticamente para facilitar el resumen), RUT o ID de trabajador, Nombre, Turno o Jornada Pactada, Hora Entrada, Hora Salida y Minutos o Tiempo de Colación. Evita combinar celdas y asegúrate de que las horas se ingresen usando dos puntos (por ejemplo, 08:30 y no 8.30).
- Crea los encabezados en la fila 1 de la hoja Registro: Fecha, ID, Trabajador, JornadaPactada, Entrada, Salida, Colacion.
- Aplica formato Fecha corta a la columna Fecha y formato hh:mm a JornadaPactada, Entrada, Salida y Colacion.
- Agrega una validación de datos en Entrada y Salida (Datos > Validación de datos > Hora, entre 00:00 y 23:59) para bloquear textos mal escritos.
- Convierte el rango en Tabla con Ctrl + T y nómbrala tblAsistencia.
3. Calcular horas trabajadas incluyendo turnos que cruzan la medianoche
Si un trabajador entra a las 08:30 y sale a las 18:00, restar Salida menos Entrada da 09:30 sin mayor problema. Sin embargo, cuando alguien entra a las 22:00 y sale a las 06:00 del día siguiente, una resta simple (06:00 - 22:00) entrega -0,6667. Excel no puede mostrar tiempos negativos en formato de hora estándar y llena la celda de símbolos #######.
Para resolver turnos diurnos y nocturnos con una única fórmula limpia, la función RESIDUO (MOD en inglés) es la herramienta ideal. Como un día completo vale 1, al calcular RESIDUO(Salida - Entrada; 1) Excel envuelve cualquier diferencia negativa sumándole 1 automáticamente, mientras que deja intactas las diferencias positivas del mismo día.
Si además descuentas el tiempo de colación (ingresado como 00:45 o 01:00 en la columna Colacion), conviene envolver el resultado con MAX(0; ...) para evitar resultados negativos si alguien registró por error una entrada y salida idénticas o una colación mayor al turno. Recuerda dar formato [h]:mm a esta columna calculada.
=MAX(0; RESIDUO([@Salida]-[@Entrada]; 1) - [@Colacion])Usa referencias estructuradas de Tabla. Si no usas Tabla de Excel, reemplaza [@Salida], [@Entrada] y [@Colacion] por las celdas de la fila (ej. F2, E2, G2). Aplica formato [h]:mm.
=SI(O([@Entrada]=""; [@Salida]=""); 0; MAX(0; RESIDUO([@Salida]-[@Entrada]; 1) - [@Colacion]))Evita que aparezcan horas ficticias mientras la fila aún no tiene marcada la salida del trabajador.
4. Separar horas ordinarias, horas extra y déficit diario
Con las horas netas trabajadas ya calculadas en una columna (llamémosla HorasTrabajadas), el siguiente paso es comparar ese tiempo contra la jornada pactada de ese día (columna JornadaPactada, por ejemplo 08:00 o 09:00). Hacerlo en columnas separadas para horas extra y horas faltantes (o atrasos) te permite sumar cada concepto por separado a fin de mes.
Si un trabajador tiene pactadas 08:00 horas diarias y trabajó 09:30, su excedente es 01:30 y su déficit es 00:00. Si trabajó 07:45, su excedente es 00:00 y su déficit es 00:15. Usar la función MAX(0; ...) garantiza que nunca aparezcan valores negativos que rompan el formato [h]:mm ni se compensen automáticamente horas extra con atrasos si tu política interna exige llevarlos separados.
También es frecuente medir el atraso específico respecto a la hora de ingreso pactada (por ejemplo, entrada esperada a las 08:30). Si agregas una columna HoraEntradaPactada, puedes calcular los minutos de atraso en la entrada de forma independiente al total trabajado al final del día.
=MAX(0; [@HorasTrabajadas] - [@JornadaPactada])Aplica formato [h]:mm. Si la persona trabajó menos de su jornada, devuelve 00:00.
=MAX(0; [@JornadaPactada] - [@HorasTrabajadas])Útil para detectar jornadas incompletas antes de cerrar el período.
=SI([@Entrada]=""; 0; MAX(0; [@Entrada] - [@EntradaPactada]))Válido para turnos diurnos donde EntradaPactada y Entrada ocurren en el mismo día calendario.
5. Crear el resumen mensual por trabajador con SUMAR.SI.CONJUNTO
Para obtener el reporte mensual sin filtrar manualmente cada persona, crea una segunda hoja llamada Resumen. Primero, vuelve a tblAsistencia y agrega una columna auxiliar llamada Mes con la fórmula =FECHA(AÑO([@Fecha]); MES([@Fecha]); 1). Esta fórmula transforma cualquier fecha del mes (como 14-11-2026) en el primer día de ese mes (01-11-2026). Dale formato personalizado mmm-aaaa para leerla como nov-2026.
En la hoja Resumen, coloca una lista única de trabajadores (puedes generarla automáticamente en Microsoft 365 con UNICOS, o copiar los IDs y nombres) y en la parte superior define una celda para seleccionar el mes a consultar (por ejemplo, C2 con el valor 01-11-2026 y formato mmm-aaaa).
Luego utiliza SUMAR.SI.CONJUNTO para sumar las horas trabajadas, las horas extra y el déficit filtrando simultáneamente por el ID del trabajador y por el mes seleccionado. No olvides aplicar el formato [h]:mm a todas las columnas de totales del resumen para ver cifras reales como 176:30 o 12:45.
=FECHA(AÑO([@Fecha]); MES([@Fecha]); 1)Normaliza todas las fechas de un mismo mes a un único valor comparable.
=SUMAR.SI.CONJUNTO(tblAsistencia[HorasTrabajadas]; tblAsistencia[ID]; A5; tblAsistencia[Mes]; $C$2)A5 contiene el ID del trabajador en la hoja Resumen y $C$2 el mes consultado. Aplica formato [h]:mm a la celda.
=SUMAR.SI.CONJUNTO(tblAsistencia[HorasExtra]; tblAsistencia[ID]; A5; tblAsistencia[Mes]; $C$2)Misma estructura apuntando a la columna HorasExtra. Aplica formato [h]:mm.
6. Consideraciones prácticas y consulta de normativa vigente
Una planilla de Excel es una excelente herramienta de análisis, control interno y validación previa al cierre de remuneraciones, pero conviene tener presentes dos aspectos clave antes de usarla en producción. Primero, protege con bloqueo de celdas las columnas que contienen fórmulas para evitar que alguien sobreescriba un cálculo por accidente al ingresar una marca.
Segundo, recuerda que en Chile las condiciones sobre jornada ordinaria, límites de horas extraordinarias, sistemas de registro de asistencia autorizados y recargos legales están reguladas por el Código del Trabajo y dictámenes administrativos. Antes de definir parámetros de jornada o implementar un registro oficial de asistencia, consulta siempre la normativa vigente y las orientaciones oficiales en el sitio de la Dirección del Trabajo.
- Bloquea las columnas calculadas (HorasTrabajadas, HorasExtra, Deficit, Mes) y deja editables solo las columnas de entrada manual.
- Mantén copias de respaldo mensuales antes de cerrar el período de remuneraciones.
- Verifica directamente en la Dirección del Trabajo (dt.gob.cl) los requisitos aplicables a tu tipo de empresa y jornada.
Errores comunes y cómo resolverlos
Diagnóstico rápidoEl total mensual muestra 08:00 o 16:00 en lugar de 176:00
Causa
La celda de total tiene formato hh:mm, que oculta los múltiplos de 24 horas y muestra solo el resto del último día.
Cómo resolverlo
Cambia el formato de la celda a Personalizado > [h]:mm para visualizar las horas acumuladas reales.
Aparecen símbolos ####### en turnos nocturnos
Causa
Se restó Salida - Entrada de forma directa (ej. 06:00 - 22:00), generando un número negativo que Excel no puede representar como hora.
Cómo resolverlo
Reemplaza la resta simple por =RESIDUO(Salida-Entrada; 1) para manejar automáticamente el cruce de medianoche.
Error #¡VALOR! al restar entrada y salida
Causa
Al menos una hora se ingresó como texto (por ejemplo, usando punto '08.30' o espacio adicional en vez de dos puntos '08:30').
Cómo resolverlo
Corrige el dato a formato HH:MM con dos puntos y agrega Validación de datos de tipo Hora para impedir ingresos inválidos.
SUMAR.SI.CONJUNTO devuelve 00:00 aunque hay registros en la tabla
Causa
La celda del mes en el resumen tiene una fecha distinta al primer día del mes o está escrita como texto ('Noviembre') en lugar de una fecha real.
Cómo resolverlo
Asegúrate de que tanto la columna Mes de la tabla como la celda filtro del resumen guarden el día 1 del mes (ej. 01-11-2026) con formato mmm-aaaa.
Plantilla de control de horas y horas extra en Excel
Libro con hoja de registro diario estructurada, cálculo automático de turnos nocturnos con RESIDUO, separación de horas extra y hoja de resumen mensual por trabajador lista para usar.
Preguntas frecuentes
¿Cómo paso las horas extra de formato [h]:mm a pesos para calcular el pago?
Multiplica el total de horas en formato [h]:mm por 24 para convertirlo a horas decimales (por ejemplo, 01:30 pasa a 1,5) y luego multiplica por el valor de la hora correspondiente. Si la celda queda en formato hora, cámbiala a Moneda o Número.
¿Qué hago si un trabajador hace dos turnos en el mismo día?
Como cada fila de la tabla representa una marca de entrada y salida, simplemente agrega una segunda fila con la misma fecha y trabajador para el segundo bloque. SUMAR.SI.CONJUNTO sumará ambas filas sin problema en el resumen mensual.
¿Sirve esta planilla si en mi empresa la colación no se descuenta de la jornada?
Sí. Si la colación forma parte de la jornada o se controla aparte, deja la columna Colacion en 00:00 o elimínala de la fórmula dejando únicamente =RESIDUO([@Salida]-[@Entrada]; 1).
¿Puedo registrar fecha y hora juntas en la misma celda?
Sí, si trabajas con turnos que duran más de 24 horas continuas (por ejemplo, guardias o faenas de 36 horas), es obligatorio incluir fecha y hora juntas (dd-mm-aaaa hh:mm) y usar una resta directa Salida - Entrada, ya que RESIDUO(..., 1) asume turnos menores a 24 horas.
¿Esta planilla reemplaza el reloj control exigido por la Dirección del Trabajo?
No. Excel sirve para gestión interna, análisis y revisión de cálculos, pero si tu empresa requiere un sistema de registro y control de asistencia formal, debes revisar las exigencias y resoluciones vigentes en dt.gob.cl.
Fuentes oficiales y recursos
- Dirección del Trabajo de Chile — Normativa y consultas laborales(Dirección del Trabajo)
- Referencia de funciones de hoja de cálculo en Excel(Microsoft Learn)
Progreso: 0 / 6 pasos
0%