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.

    Responder
  2. juan

    Me gusto esta lección de Exel.Gracias

    Responder
  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

    Responder
  4. Patricio Chamorro

    Excelente, muy útil!

    Responder
  5. Horacio Carmona

    Excelentissimo aporte al curriculum, gracias Moy!!!

    Responder
  6. Jorge A Rodríguez Miranda

    Gracias amigo una excelente aportación para nuestras rutinas diarias

    Responder
  7. luis parra

    Gracias por la aportacion me sirvio y lo aplicare mil gracias

    Responder
  8. Guillermo Alvarez O.

    Una vez más GRACIAS

    Responder
  9. 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;

    Responder
  10. felipe

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

    Responder
  11. Manuel

    Amigo Moisés, te agradecemos el valioso aporte

    Responder
  12. Raul Ortiz

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

    Responder
  13. jorge rocha

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

    Responder
  14. 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.

    Responder
  15. jose luis gustavo

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

    Responder
  16. 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

    Responder
  17. Augusto

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

    Responder
  18. Gilberto Recuero

    claro y bien explicado, gracias amigo

    Responder
  19. edgar

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

    Responder
  20. 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

    Responder
    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”.

      Responder
  21. Eunice

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

    Responder
  22. Yomaris

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

    Responder
    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).

      Responder
  23. Kevin

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

    Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>