Calcular horas extras en Excel

Hace algunos meses escribí un artículo sobre cómo calcular las horas de trabajo en Excel entre dos fechas, sin embargo, existen algunas mejoras que podemos implementar a dicho ejemplo, como el cálculo de horas extras y el pago de las mismas.

Observaciones importantes

A lo largo de este artículo desarrollaré tres ejemplos. Para el primero de ellos consideraré una hora extra cuando se sobrepasa la cantidad de horas laborables regulares en un solo día. El segundo ejemplo es una pequeña variación del primero para considerar aquellos horarios de trabajo nocturnos que implican un cambio de fecha.

El tercer ejemplo hace el cálculo de horas extras pero en base a un criterio semanal y no diario como los primeros dos ejemplos. Así que sin más que aclarar, comencemos a desarrollar el primer ejemplo.

Preparación de los datos

Para este ejemplo utilizaré una tabla de datos que contiene las horas de entrada y salida para cada uno de los días laborables, así como el horario en que se ha tomado un descanso.

En las celdas B2 y B3 se encuentra el horario laboral. La celda B5 tiene la cantidad de tiempo permitido para el descanso diario y junto con los valores anteriores podremos obtener la cantidad de horas efectivas de trabajo diario en la celda B6.

Las celdas E1 y E2 contienen la tarifa que deberá pagarse por cada hora, ya sea en horario regular o como horas extras.

Calcular el total de horas trabajadas

El primer cálculo que realizaremos será el total de horas trabajas para cada uno de los días y para eso utilizaré la siguiente fórmula:

=(E10-D10)+(C10-B10)

Esta fórmula hace una resta entre la hora de salida y el fin de descanso y lo suma con la diferencia de horas entre el inicio del descanso y la hora de entrada. Observa el resultado al aplicar esta fórmula para todos los días:

Con esto obtenemos las horas de trabajo diario y los siguientes cálculos estarán basados en este valor.

Calcular las horas regulares

El total de las horas que deben laborarse por día lo hemos calculado en la celda B6 y para saber si cada día se ha cumplido dicha cantidad de horas de trabajo utilizaré la función SI haciendo una comparación con el total de horas trabajadas con la siguiente fórmula:

=SI(F10>$B$6,$B$6,F10)

Si el total de las horas trabajadas en el día es mayor que las horas que deben laborarse, entonces hemos cumplido con la cantidad de horas regulares requeridas. De lo contrario, las horas regulares de dicho día son menores. Esta lógica quedará clara con la siguiente imagen:

Ya que para la mayoría de los días tenemos más de 8 horas en la columna Total horas, entonces la columna Horas regulares mostrará el valor 8:00, sin embargo, para el último día la condición no se cumple y por lo tanto las horas regulares serán las mismas que el total de horas.

Calcular horas extras en Excel

El siguiente paso es calcular las horas extras y que de nueva cuenta será un condicional para saber si la columna Total horas excede el mínimo requerido. La fórmula que utilizaré será la siguiente:

=SI(F10>$B$6,F10-$B$6,0)

Si la columna Total horas no excede el mínimo requerido, entonces no hay horas extras, que para nuestro ejemplo se cumple en el último día. Observa el resultado de aplicar esta fórmula:

Obtener total de horas

Solo faltará realizar la suma de las columnas Horas regulares y Horas extras para conocer el total y poder calcular el pago. La suma es tan simple como utilizar la función SUMA. Para que Excel muestre el total de manera correcta debo utilizar el formato personalizado [hh]:mm para ambas celdas:

Si no estás muy seguro de cómo aplicar este formato te recomiendo consultar el artículo: Sumando horas en Excel.

Calcular el pago de horas

Para poder calcular el monto a pagar por las horas regulares y las horas extra es necesario convertirlas a un valor decimal que pueda ser multiplicado por la tarifa correspondiente. Para hacer dicha conversión es suficiente con multiplicar el total por 24. Si tienes duda de por qué se debe multiplicar por dicho valor, puedes consultar una explicación en el artículo Convertir un número decimal a minutos que describe el proceso inverso.

Una vez que tenemos una equivalencia de las horas en valor decimal solo nos faltará hacer la multiplicación por las tarifas adecuadas para obtener el total a pagar:

De esta manera hemos realizado el cálculo de horas extras en Excel así como el pago de las mismas. A continuación mostraré una variante de este método cuando tenemos un horario de trabajo que se extiende al día siguiente.

Calcular horas extras en horario nocturno

La modificación que vamos a hacer del ejemplo anterior será útil para aquellos horarios de trabajo nocturno y que implican un cambio de fecha dentro de la misma jornada laboral. Lo que debemos hacer es agregar la fecha para cada eventualidad de nuestra hoja, es decir, para el inicio y final del descanso así como para la hora de salida:

Una vez ingresados estos datos la única fórmula que debemos modificar es para la columna Total horas que deberá ser la siguiente:

=((G10+H10)-(E10+F10))+((C10+D10)-(A10+B10))

Si observas con detenimiento, esta fórmula combina las fechas con las horas y posteriormente obtiene las diferencias entre todas dejándonos como resultado el total de horas trabajadas en una jornada laboral. No es necesario modificar el resto de fórmulas del ejemplo anterior para obtener el resultado correcto:

Calcular horas extras por semana

Hasta ahora hemos calculado las horas extras por cada día laboral, es decir, si en un día se trabaja por nueve horas, la última hora ya es considerada una hora extra y es pagada bajo dicha tarifa. Sin embargo, es probable que en tu negocio o empresa se tenga una regla diferente y se tenga una cuota semanal (o mensual) de horas regulares a partir de la cual se debe realizar el cálculo de horas extras. Considera el siguiente ejemplo:

La tabla de datos es la misma tabla del primer ejemplo con la diferencia que la suma de horas se obtiene en la columna Total horas. A partir de dicho valor hacemos el cálculo de la horas regulares y horas extras siguiendo un procedimiento muy similar al de los ejemplos anteriores. En este caso, solo se pagarán las horas extras que sobrepasen la cuota semanal establecida en la celda B2.

Con estos ejemplos tendrás una buena base para realizar tu propio cálculo de horas extras en Excel y personalizar esta solución en base a tus propios requerimientos. Descarga el libro de trabajo y continúa trabajando con los ejemplos desarrollados.