Planilla de horas extras en Excel: cómo hacerla paso a paso

Una planilla de horas extras es un libro de Excel que convierte las horas que un empleado trabajó de más en dinero, aplicando los recargos que fija la ley. Aquí vas a montar una con tres hojas: Datos, Parámetros y Liquidación, con fórmulas que calculan el valor de la hora ordinaria, separan horas diurnas y nocturnas por minutos y traen los porcentajes correctos con BUSCARV. Funciona en Excel para Microsoft 365, 2024, 2021 y 2019; en versiones más antiguas las funciones se llaman igual.

Para qué sirve una planilla de horas extras y qué partes la componen

Imagina la quincena de un empleado que laboró 8 horas extras diurnas entre semana, 4 nocturnas y 6 en domingo. Saber cuánto se le paga no es sumar y multiplicar: cada tipo de hora lleva un recargo distinto sobre el valor de la hora ordinaria, y ese valor se calcula dividiendo el salario mensual entre las horas legales del mes. Una planilla bien hecha aplica esos recargos sin equivocarse y deja trazabilidad para una visita del Ministerio del Trabajo.

El libro que vas a construir tiene tres pestañas. La primera, Datos, recoge las marcaciones de cada empleado con fecha, hora de entrada, hora de salida y tipo de hora. La segunda, Parámetros, guarda el salario, las horas del mes y los porcentajes por tipo. La tercera, Liquidación, resume cuánto debe recibir cada persona y el total devengado. Las reglas que siguen los cálculos vienen del Código Sustantivo del Trabajo colombiano: los artículos 161 y 167-A definen el valor de la hora ordinaria, los recargos por trabajo nocturno, dominical o festivo y los topes de jornada. La Ley 2466 de 2025 y la Ley 2101 de 2021 actualizan esos porcentajes y la jornada legal.

Las reglas que la planilla tiene que saber antes de calcular

La jornada legal ordinaria pasó de 48 a 42 horas semanales desde el 15 de julio de 2026 (Ley 2466 de 2025). Eso cambia la fórmula del valor de la hora: ya no se divide entre 240, sino entre 210. La operación exacta es salario_mensual / (42/6 * 30), porque una semana laboral de 42 horas equivale a 7 horas diarias y el mes tiene 30 días.

Cada hora extra tiene un tope: como máximo 2 horas extras al día y 12 a la semana (artículo 161 del CST). Si se superan, las que pasen se consideran autorización del Ministerio del Trabajo o sanción.

El horario nocturno va de las 19:00 a las 06:00 del día siguiente, sin importar que el empleado haga turno mixto. Cada hora trabajada en esa franja lleva un recargo distinto. Los domingos y festivos son días de descanso remunerado, así que cualquier hora laborada en esos días suma recargo dominical aparte de si es diurna, nocturna o extra.

Para la planilla, estos seis tipos son los que tienen recargos diferentes:

Los porcentajes según el tipo de hora

El recargo dominical del 90 % está vigente desde el 1 de julio de 2026 (Ley 2466 de 2025). Antes de esa fecha era del 75 %.

La estructura de la hoja: una pestaña por cada cosa

Antes de escribir fórmulas, crea las tres hojas. Ve a la parte inferior, haz clic derecho sobre la pestaña Hoja1 y elige Insertar > Hoja de cálculo dos veces. Renombra la primera como Datos, la segunda como Parámetros y la tercera como Liquidación. En la hoja Parámetros pondrás lo que no cambia de un empleado a otro: salario, horas del mes y la tabla de porcentajes.

La celda B4 está calculada; la fórmula real la montamos en el siguiente paso. La tabla de la derecha (E1:G6) es la fuente de porcentajes que se consultará con BUSCARV más adelante.

El valor de la hora ordinaria con la función de la división

El valor de la hora ordinaria es la pieza clave: todo lo demás se calcula multiplicando horas por ese valor y por el recargo. La fórmula en la celda B4 divide el salario entre las horas legales del mes.

1. En la hoja Parámetros, sitúate en B4.
2. Escribe la fórmula y pulsa Entrar.

=B1/B3

Al pulsar Entrar debería verse 8.338, que es el valor de la hora ordinaria para un salario mínimo mensual legal vigente en 2026 (1.750.905 pesos) sin contar el auxilio de transporte. Si quieres incluir el auxilio porque la jornada es completa, divide (B1+B2)/B3 y obtienes 9.524. La diferencia es grande: por eso conviene dejar el auxilio separado y sumarlo solo cuando aplique.

Horas que se acumulan más allá de 24 con el formato [h]:mm

Un problema típico: un empleado tiene 28 horas extras en el mes, pero al sumarlas aparece 4:00 porque el formato estándar reinicia a medianoche. La solución está en el formato de celda, no en la fórmula.

1. En la hoja Datos, escribe las horas extras de tres empleados en C2, C3 y C4.
2. Selecciona el rango C2:C4.
3. Abre Formato de celdas con Ctrl+1.
4. En la pestaña Número, elige Categoría Personalizada y, en el cuadro Tipo, escribe [h]:mm.
5. Pulsa Aceptar.

Al hacer clic en C4 verás en la barra de fórmulas el valor real 10:00, y al sumar C2:C4 con Autosuma (Alt+=) el resultado es 25:30. Si solo ves 1:30, el formato sigue siendo h:mm; repite el paso 4.

Calcular la hora extra diurna y la nocturna con dos tablas de BUSCARV

Con las horas ya acumuladas, toca traer el porcentaje correcto para cada fila. La función BUSCARV (en inglés VLOOKUP) busca la clave de tipo en la tabla de Parámetros y devuelve el recargo correspondiente.

En la hoja Datos, añade tres columnas: clave de tipo, porcentaje traído y valor en pesos.

1. En la columna F (clave) copia los mismos valores de D: 1, 2 o 3.
2. En la columna G (recargo) escribe la fórmula y arrástrala hacia abajo con el controlador de relleno.

=BUSCARV(F2;Parámetros!$E$2:$G$6;3;FALSO)

El cuarto argumento, FALSO, obliga a buscar coincidencia exacta: si la clave no existe, devuelve #N/A y así sabes que falta tipificar la fila.

Horas que se reparten entre día y noche por minutos

Cuando un turno cruza las 19:00, las horas se parten: la parte de antes de las 19:00 es diurna y la de después, nocturna. Excel lo calcula comparando la entrada y la salida contra la hora frontera. La fórmula usa fracciones de día: 19:00 es 19/24.

Para una fila con entrada a las 20:30 y salida a las 23:00, la parte nocturna son 2 horas y 30 minutos:

=MAX(0;MIN(salida;19/24)-MAX(entrada;19/24))

La función MAX (en inglés MAX) evita valores negativos; MIN (en inglés MIN) corta la jornada en la frontera. Si quieres cobrar por fracciones de 15 minutos, multiplica el resultado por 96 (96 cuartos de hora en un día), redondea con MULTIPLO.INFERIOR.MAT (FLOOR) a 1 y divide entre 96 de nuevo.

=MULTIPLO.INFERIOR.MAT(MAX(0;MIN(salida;19/24)-MAX(entrada;19/24))*96;1)/96

El cálculo final de cada tipo de hora en una sola columna

Ahora se juntan horas, valor hora y porcentaje en una sola columna. En la hoja Datos, columna H, escribe:

=C2*Parámetros!$B$4*G2

Pulsa Entrar, formatea H2 como Contabilidad con dos decimales y arrastra hasta H4. Para el total por empleado, en H5 pon =SUMA(H2:H4).

El formato Contabilidad alinea el símbolo a la izquierda y el número a la derecha, lo que ayuda a revisar grandes liquidaciones.

Por qué el resultado da diferente a la calculadora del Ministerio

Cuando la planilla arroja un valor distinto al que publica Actualícese o al que calcula el Ministerio del Trabajo, casi siempre la causa está en cuatro columnas concretas. Vamos una por una.

Salario con auxilio incluido: si divides 1.750.905 entre 210 obtienes 8.338, pero si sumas 249.095 al numerador para incluir el auxilio de transporte, da 9.524. La calculadora oficial separa salario y auxilio, así que revisa B1 y B2 de Parámetros.

Divisor de 220 en lugar de 210: la fórmula del valor hora usa 240 cuando la jornada era de 48 horas. Con la Ley 2466 de 2025 ya rige 42 horas semanales, equivalentes a 210 horas al mes; usa siempre 210 desde julio de 2026.

Formato h:mm en vez de [h]:mm: si una columna de horas suma 25:30 pero aparece 1:30, el formato corta a las 00:00. Vuelve a aplicar [h]:mm desde Ctrl+1 > Personalizada.

BUSCARV con último argumento VERDADADERO: si dejas la coincidencia aproximada activada, Excel devolverá el porcentaje más cercano sin avisar. Mantén siempre FALSO para que las claves sin coincidencia den #N/A.

Cerrar la quincena con un resumen para cada empleado

La hoja Liquidación debe condensar, empleado por empleado, cuántas horas se laboraron de cada tipo y cuánto se pagó en total.

1. En Liquidación, escribe los encabezados en la fila 1: Empleado, Horas ordinarias, Horas extras diurnas, Horas extras nocturnas, Horas dominicales, Total extras, Salario base, Total devengado.
2. En la fila 2, trae los datos con SUMAR.SI.CONJUNTO (en inglés SUMIFS) desde la hoja Datos.

=SUMAR.SI.CONJUNTO(Datos!C2:C100;Datos!A2:A100;A2;Datos!F2:F100;1)

1. Al final, inserta una Tabla dinámica (Insertar > Tablas > Tabla dinámica) con origen en Datos y arrastra Empleado a Filas, Clave a Columnas y Horas a Valores.

Lo que ya sabes hacer con la planilla de horas extras y el siguiente paso

• Tres hojas separadas: Datos, Parámetros y Liquidación, con responsabilidades distintas.
• Valor hora como fórmula automática, editable desde una sola celda.
• Formato [h]:mm para acumular más de 24 horas sin reiniciar a medianoche.
• BUSCARV con referencias absolutas que trae cada porcentaje desde la tabla de tipos.
• Hoja Liquidación con totales por empleado y Tabla dinámica para auditoría.

El siguiente paso es validar los datos del horario con la herramienta Datos > Herramientas de datos > Validación de datos, creando una lista desplegable con las claves 1 a 5 para que nadie digite un tipo fuera del catálogo.

Recursos oficiales para comprobar la normativa y las plantillas

La calculadora interactiva de Actualícese reproduce el cálculo oficial con los valores vigentes en cada año y permite descargar una plantilla base. El texto consolidado del artículo 167-A del Código Sustantivo del Trabajo está disponible en la Secretaría del Senado de Colombia, donde también se consultan las Leyes 2101 de 2021 y 2466 de 2025 que actualizan la jornada y los recargos. La plantilla de HoraClara, del Ministerio del Trabajo, se usa como referencia para auditorías porque replica exactamente los criterios de la inspección laboral.

Esta planilla no calcula seguridad social, prestaciones sociales ni retenciones en la fuente; esas columnas se suman en otro archivo porque dependen de la situación particular de cada empleado.

Cuándo esta planilla se queda corta y qué usar en su lugar

Cuando la organización pasa de veinte empleados o maneja turnos rotativos, la digitación manual de cada hora se vuelve el principal riesgo de error. Lo razonable es migrar a un sistema con marcaciones electrónicas que exporte un archivo .csv quincenal. Ese archivo se carga con Datos > Obtener y transformar > Desde texto/CSV (Power Query), presente en Excel 2016 y siguientes, y se conecta directo a las hojas Datos y Liquidación sin tipear.

Dudas al montar la planilla de horas extras