Serie Ejercicios de Excel Avanzado

Ejercicio #5

Nivel de dificultad: 6/10

Cálculo de horas laborales extras ordinarias diurnas

Índice de contenido

Planteamiento del problema
Solución del problema

PLANTEAMIENTO DEL PROBLEMA

(clic para ver tamaño completo)

Figura 1.

Se requiere diseñar un libro de Excel para calcular las horas laborales extras ordinarias diurnas de los empleados.

En una hoja de Excel se deben registrar las horas de entrada y salida del empleado en diferentes fechas.

Se debe mostrar el total de horas trabajadas en la jornada por cada empleado en cada fecha, así como por supuesto las horas extras.

Finalmente, debe mostrarse el total de horas extras laboradas por el empleado y el monto de dinero correspondiente a ese total.

Puedes DESCARGAR AQUÍ el libro de Excel completo con todo terminado.

SOLUCIÓN A ESTE PROBLEMA DE EXCEL

Aunque el planteamiento está enfocado en Colombia, he parametrizado ciertos valores como para que el libro pueda ser usado en otro país, con pequeños ajustes.

Funciones a utilizar:

SI
O
SI.ERROR
REDONDEAR
BUSCARV
DIASEM
TEXTO
ENTERO

Paso 1. Establecer los datos necesarios para los cálculos

Como se ilustra arriba en la figura 1, todas las celdas en color amarillo representan celdas donde se debe ingresar valores.

Aparte de registrar la fecha y la hora de entrada y salida también se debe ingresar lo siguiente:

Salario mensual
Duración de la jornada ordinaria
Porcentaje de recargo para las horas extras diurnas ordinarias (según las leyes laborales en Colombia)
Tiempo de descanso

Paso 2. Cálculo de la cantidad total de horas laboradas en cada jornada

Para este cálculo debemos hacer la resta entre la hora de salida y la hora de entrada, siempre y cuando ambas horas sean válidas. Dicha diferencia debe ser multiplicada por 24 para obtener las horas. Debes tener presente que la celda donde estará esta fórmula debe tener formato “General”.

De esta forma, en la celda E9 de la hoja “Empleado 1” del libro de Excel que puedes descargar arriba, tenemos la fórmula que permite calcular la cantidad total de horas laboradas en cada jornada:

=SI(O(C9=“”;D9=“”);””;(D9-C9)*24)

Paso 3. Cálculo de la cantidad total de horas extras diurnas diarias

Lo primero a tener en cuenta para este cálculo es saber que el mismo se realiza solo si se ha superado la cantidad de horas que exige la jornada ordinaria, además de verificar que las horas de entrada y salida sean válidas. Esto involucra el uso de la función SI y de la función SI.ERROR, tal como te muestro a continuación en la fórmula de la celda G9:

=SI.ERROR(SI((E9-8)>0;E9-D$2-(B$5+D$5/60);“”);“”)

En la fórmula anterior se resta la duración de la jornada y el tiempo de descanso del total de horas laboradas.

La división por 60 es porque los minutos de descanso de la celda D5 deben ser convertidos a horas con la finalidad de poder sumar con las horas de descanso en la celda B5.

Paso 4. Cálculo de la cantidad total de horas extras en el intervalo de fechas

Aquí simplemente lo que hacemos es sumar todas las horas extras diurnas de cada fecha. En la fórmula de la celda G25 hemos hecho un redondeo a 2 decimales usando la función REDONDEAR:

=REDONDEAR(SUMA(G9:G24);2)

Paso 5. Cálculo del monto total de dinero correspondiente al total de horas extras

Para este cálculo se debe multiplicar el total de horas extras del período por el valor de la hora ordinaria y al resultado sumarle el porcentaje correspondiente por recargo de hora extra diurna ordinaria, tal como se muestra en la fórmula de la celda G26:

=REDONDEAR(G25*(D1/240)*(100%+D3);0)

La división del salario mensual por 240 (horas) es para obtener el costo de la hora ordinaria. En Colombia la jornada mínima diaria de trabajo contempla máximo 8 horas diarias, lo que al considerar un mes de 30 días, nos da 240

En la fórmula, unos de los elementos a multiplicar es la suma de 100% más el porcentaje de recarga para horas extras en días no festivos.

Ejercicio 5 de Excel Avanzado