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.

Calcular horas extras en Excel

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.

Planilla de Excel para el cálculo de horas extras

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:

Liquidador de horas en Excel

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:

Control de horas extras en Excel

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:

Cómo calcular horas extras en Excel

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:

Cálculo de horas extras semanales en Excel

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:

Calcular horas extra con Excel

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:

Cómo calcular horas extras en una hoja de cálculo

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:

Fórmula en Excel para calcular horas extras

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:

Planilla para calcular horas extras en Excel

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.

28 pensamientos en “Calcular horas extras en Excel

  1. LUIS ROMERO GONZALES

    Es una buena alternativa para llevar el control de las horas extras.
    Gracias.

  2. juan

    Me gusto esta lección de Exel.Gracias

  3. Saraí Torres

    Gracias me gusto mucho lo del horario nocturno justo habia estado batallando con eso, usted siempre pensando en todo y yendo un paso adelante

  4. Patricio Chamorro

    Excelente, muy útil!

  5. Horacio Carmona

    Excelentissimo aporte al curriculum, gracias Moy!!!

  6. guille

    excelente!!

  7. Jorge A Rodríguez Miranda

    Gracias amigo una excelente aportación para nuestras rutinas diarias

  8. luis parra

    Gracias por la aportacion me sirvio y lo aplicare mil gracias

  9. Guillermo Alvarez O.

    Una vez más GRACIAS

  10. Nandu Bernal

    Muchas gracias por su importante aporte; tengo una inquietud, al sumar las horas regulares me da como resultado; 15:57 en vez de 39:57, al parecer cuando llega a 24:00 inicia con 00:00 y me toma las restantes, agradezco me informe si es posible como puedo corregir dicha situación. Muchas gracias

    Saludos;

    1. Moisés Ortíz Autor

      Hola Nandu, se debe al formato de la celda, debes cambiarlo por [hh]:mm
      Para más información consulta el siguiente artículo:
      http://exceltotal.com/sumando-horas/

  11. felipe

    Es muy claro y con instrucciones explicitas .para que cualquier persona ,las entienda y las pueda asimilar.

  12. Manuel

    Amigo Moisés, te agradecemos el valioso aporte

  13. Raul Ortiz

    Como siempre tus ejemplos son excelentes y explicados de forma que todos podamos entenderlos.
    Gracias por tu ayuda.

  14. jorge rocha

    excelente explicacion, muy agradecido con los conocimientos q nos ofrece y muchas por poder aprenderlos.
    cordialmente
    jorge rocha

  15. Jose

    Excelente ayuda se les agradece, el día de hoy me entere de esta pagina, deberíamos difundirla para que ayude a mas gente.

  16. jose luis gustavo

    excelente explicación, ya no llegare tarde al trabajo. favor unas clases básicas de macros

  17. Angélica

    MUUUCHAS GRACIAS MOISÉS…..UN TREMENDO APORTE, NO TENÍA IDEA QUE PODÍA COMBINAR FECHAS Y HORAS EN UNA FÓRMULA, YO HACÍA ESTE CÁLCULO DE MANERA MUY RUDIMENTARIA, AHORA SERÁ MUY PRECISO. MUCHAS, MUCHAS GRACIAS.

    ANGÉLICA

  18. Augusto

    muy interesante Moisés Ortíz gracias por el aporte

  19. Gilberto Recuero

    claro y bien explicado, gracias amigo

  20. edgar

    excelente aporte , de muy fácil entendimiento y muy completo ..
    saludos..

  21. fredy suarez

    cuando la persona ingresa a laborar a las 18:00 y sale a las 06:00 del otro dia, se genera un error de ##########, como se puede corregir eso?, mil gracias

    1. Moisés Ortíz Autor

      Hola Fredy, ya que son horas de días diferentes es necesario que involucres el dato de la fecha, tal como lo hago en la sección “Calcular horas extras en horario nocturno”.

  22. Eunice

    Buenisimo este tutorial, excelente página muchas felicitaciones por este aporte tan grande.

  23. Yomaris

    Cuando entro la formula =SI(F13>$C$6,F13-$C$6,O) me sale #NAME? Como puedo resolver esto?

    1. Moisés Ortíz Autor

      Hola Yomaris, se debe a que tienes Excel en inglés y por lo tanto deberás utilizar los nombres de las funciones en inglés. En este caso debes utilizar la función IF (en lugar de SI).

  24. Kevin

    Quiero dar las gracias por este aporte y felicitar al autor por compartir sus conociemintos de forma sencilla para todos!