Control del horario de los empleados con Excel: guía fácil

Cada vez más empresas adoptan horarios flexibles de trabajo, algo que, si bien tiene grandes ventajas para la productividad y la motivación de los empleados, ha de apoyarse en una cierta base de confianza. Los sistemas de registro de la jornada laboral son un instrumento para lograrlo, porque, al tiempo que acredita las horas trabajadas, sirve a la dirección para controlar la calidad y liquidar la facturación semanal o mensual. Todo lo que necesitas para gestionar una hoja para el control horario de la plantilla es el software de hojas de cálculo Excel y seguir nuestras instrucciones paso a paso.

Excel con Office 365 y 1&1 IONOS

Usa Excel para organizar tus datos y ahorrarte tiempo, ¡incluido en cada pack Office 365!

Office Online
OneDrive con 1TB
Asistencia 24/7

Calcular las horas trabajadas con Excel: cómo llevar un registro profesional

¿Qué distingue a una hoja de trabajo de Excel bien planificada? A este respecto, el empleado, el empleador e incluso la ley tienen diferentes puntos de vista.

Cómo ha de ser una hoja de trabajo para el empleado

Desde el punto de vista del empleado, un sistema de registro diario de la jornada laboral ha de ser, por encima de todo, fácil de usar. Al fin y al cabo, rellenar cada día la tabla con las horas de entrada, salida y pausas se convierte en una tarea extra y supone una interrupción de la concentración. Ya el diseño y la estructuración de la hoja de trabajo de Excel han de ser lo suficientemente claros como para que el empleado sepa de inmediato qué datos ha de introducir, cómo y dónde. Para lograrlo, diseñar una estructura fácil, diferenciar secciones o celdas con colores suaves y reducir el diseño a lo esencial puede resultar de gran ayuda. Al fin y al cabo, cuanto más sencilla y clara es una tarea, más probable es tener ganas de realizarla todos los días.

Requisitos de la hoja de trabajo para el empleador

Dado que el registro de la jornada es tan relevante para controlar la presencia del empleado, como también para el cálculo del salario, en ocasiones implica tener que utilizar fórmulas de cierta complejidad que una tabla eficiente de Excel debería incluir para aliviar la carga de trabajo de los empleados y de la dirección. Las funciones necesarias se pueden insertar en la tabla de forma invisible y reducir la tasa de errores a la larga.

El registro de la jornada en el derecho laboral

El derecho laboral español se extiende en varios decretos y leyes aprobados y reajustados a lo largo de los años. El corpus central lo constituye el Real Decreto Legislativo 2/2015, de 23 de octubre, por el que se aprueba el texto refundido de la Ley del Estatuto de los Trabajadores. Aquí se recoge la normativa que afecta a la relación laboral entre el trabajador y el empleador, incluida la duración de la jornada laboral (art. 34), que no deberá superar las 40 horas semanales y las 9 diarias, o las pausas entre el final de una jornada y el comienzo de la siguiente, que no puede ser inferior a 12 horas.

Con la entrada en vigor en mayo de 2019 del Real Decreto-ley 8/2019, de 8 de marzo, de medidas urgentes de protección social y de lucha contra la precariedad laboral en la jornada de trabajo, se añade un punto al artículo 34 en el que se especifica la obligación de las empresas a partir de este momento de garantizar el registro diario del inicio y el final real de la jornada, de organizar este sistema de registro por convenio o en colaboración con los representantes legales de los trabajadores (comité de empresa) y de conservar toda esta documentación durante cuatro años, un plazo durante el cual estarán a disposición de los empleados, sus representantes legales y la Inspección de Trabajo y Seguridad Social. Con esta medida, adoptada a raíz de la recomendación que lanzó a España el Abogado General del Tribunal de Justicia de la Unión Europea (TJUE), Giovanni Pitruzzella, y que había sido bloqueada por la Confederación Española de Organizaciones Empresariales (CEOE) repetidas veces, se aspira a poner freno a la explotación laboral en forma de horas extras no remuneradas.

Calcular las horas trabajadas con Excel: crea tu propia hoja de trabajo

En el ejemplo que mostraremos a continuación, diseñaremos la hoja de trabajo de Excel de un solo empleado, que en ella podrá introducir a qué hora inicia y finaliza su jornada laboral, así como sus pausas. Con estos datos, la hoja calculará cuántas horas reales ha trabajado e indicará, si es el caso, si ha trabajado de más o de menos. En los empleos remunerados por horas trabajadas, esto ahorrará tiempo al empleador o al departamento financiero de la empresa. Al mismo tiempo, el empleado también podrá apuntar en ella sus vacaciones y los días que se ha ausentado por enfermedad. La información más importante podrá destacarse recurriendo a las opciones de formato visual de la hoja de cálculo.

En este enlace puedes descargar la plantilla de 1&1 IONOS para registrar las horas trabajadas de tus empleados de forma gratuita:

Excel-Download

Plantilla de Excel de horas trabajadas

En las siguientes instrucciones, te revelamos cómo hemos creado nuestra plantilla.

Paso 1: Configurar el encabezado y las columnas

Para asignar toda la información necesaria a la primera fila de la tabla, configurar el diseño de las columnas y dar un formato limpio a la hoja, procede de esta manera:

  1. Introduce en la celda A1 el nombre del empleado.
     
  2. Introduce en la celda D1 la jornada diaria del empleado en el formato hh:mm (horas y minutos), p. ej., 08:00 (8 horas). Este valor servirá de referencia para las fórmulas que incorporaremos más adelante.
     
  3. Para que sea más fácil al empleado encontrar la fila correcta, añadiremos un pequeño pero práctico detalle: la fecha actual. Escribe para ello la fórmula =HOY() en la celda H1 y confirma con la tecla “Enter”.
     
  4. Otro detalle útil: en la segunda fila puedes aclarar a qué dirección de correo electrónico (p. ej., del departamento de RRHH) se ha de enviar la hoja de horas trabajadas y con qué asunto.
     
  5. En la tercera fila, titula las columnas como “Fecha”, “Inicio jornada”, “Inicio pausa”, “Final pausa”, “Final jornada”, “Ausencia”, “Horas trabajadas” y “Más/Menos”.
     
  6. A la hora de dar formato visual a la tabla, tienes cierta libertad para utilizar negrita y los distintos tipos, tamaños y colores de las fuentes. Desde el punto de vista estético, las columnas se ven más claras y atractivas si tienen el mismo ancho. Las celdas combinadas y los colores de relleno pueden contribuir a distinguir secciones de la tabla entre sí. El botón “Copiar formato” en la esquina superior izquierda de la cinta de opciones agiliza las tareas de diseño.
     
  7. Por último, fija las tres primeras filas de la tabla para que, a medida que se van rellenando las semanas, siempre permanezcan a la vista. Esto se consigue así: marca la celda A4, ve a la pestaña “Vista” y allí pulsa primero en “Inmovilizar” y luego en “Inmovilizar paneles”.
Consejo

Si lo deseas, puedes copiar todas las fórmulas que indicaremos en este artículo en tu propia tabla de Excel, pero no olvides confirmar cada entrada con la tecla “Enter” para que las fórmulas se inserten sin errores.

Paso 2: Insertar una lista de fechas en secuencia

Para crear una lista de fechas secuenciales y distinguir el fin de semana con colores, sigue estos pasos:

  1. En la celda A4, escribe el primer día, a partir del cual se ha de registrar la jornada laboral.
     
  2. En la pestaña “Inicio” y en la categoría “Número”, abre el menú desplegable y selecciona el formato “Fecha larga”. Esto hará que la fecha se muestre en formato largo, que incluye el día de la semana (quizás has de utilizar el formato de fecha dd/mm/aa para que esta función dé el resultado correcto).
     
  3. Clica en el pequeño cuadrado que aparecerá en la esquina inferior derecha de la celda A4 cuando la marques y mantén pulsado el ratón. Ahora desliza el ratón hacia abajo hasta cubrir siete celdas para una semana completa.
     
  4. Ahora marca todas columnas. Haz clic en la columna A, mantén pulsado el botón izquierdo del ratón y arrástralo hasta la columna H.
     
  5. En “Inicio” > “Estilos” pulsa en la función “Formato condicional”.
     
  6. En el menú de contexto, haz clic en “Nueva regla…”. Se abrirá una ventana separada.
     
  7. En “Nueva regla de formato”, selecciona “Utilice una fórmula que determine las celdas para aplicar formato”.
     
  8. En “Dar formato a los valores donde esta fórmula sea verdadera”, escribe la siguiente fórmula: =DIASEM($A1;2)>=6 (esto es: “Lo siguiente vale para todos los sábados y domingos en la columna A”).
     
  9. Clica en el botón “Formato”. Se abrirá una nueva ventana.
     
  10. En la pestaña “Fuente”, selecciona un color para marcar los fines de semana y confirma los ajustes. Confirma otra vez en “Aceptar”. Ahora, todos los sábados y los domingos tendrán un color asignado.

Que no cunda el pánico: todas estas acciones de formato, reglas y fórmulas solo se han de realizar una vez. Para dar estas propiedades a otras celdas, solo tienes que arrastrar el cuadrado pequeño que aparece en la esquina inferior derecha de la celda hacia abajo o copiar la celda.

Paso 3: Configurar las opciones para registrar una ausencia laboral

Este es el proceso a seguir para insertar un menú desplegable en la columna para registrar distintos tipos de absentismo laboral:

  1. En la columna F (“Ausencia”), marca la primera celda que corresponde a un día de la semana. En nuestro caso, es la F4.
     
  2. En la cinta de opciones, ve a la pestaña “Datos”.
     
  3. En “Herramientas de datos” haz clic en “Validación de datos”. Se abrirá una ventana.
     
  4. En “Configuración” > “Criterio de validación” > “Permitir” selecciona “Lista”.
     
  5. Desmarca la casilla de “Omitir blancos”.
     
  6. En el cuadro de diálogo “Origen”, escribe las opciones que han de aparecer en el menú desplegable, pero no te olvides de separarlas por punto y coma. En nuestro ejemplo sería: “Vacaciones;Enfermedad;Home office”.
     
  7. Confirma los ajustes.
     
  8. En la celda de la columna F aparecerá ahora un menú desplegable. Para dar la misma configuración a los otros días de la semana, copia los ajustes o desliza el cuadradito de la celda hacia abajo.
Nota

Para corregir su selección en el menú desplegable, el usuario solo ha de borrarla.

Paso 4: Calcular las horas trabajadas con Excel

Esta es la forma de programar a Excel para que calcule las horas trabajadas por un empleado de forma automática:

  1. Marca la columna G (“Horas trabajadas”).
     
  2. En la pestaña “Inicio” > “Número” selecciona “Más formatos de número”.
     
  3. Se abrirá una nueva ventana. En “Categoría”, selecciona “Personalizada”, y en “Tipo”, el cuadro a la derecha, selecciona el formato [h]:mm:ss (con él, ordenas a Excel que en la suma tenga en cuenta el número de horas absoluto, en lugar de comenzar otra vez desde cero después de 12:00).
     
  4. Confirma con “OK”.
     
  5. Ahora, copia esta fórmula en la primera celda de la columna G que corresponda a un día de la semana (G4, en nuestro caso): =SI(O(F4="Vacaciones";F4=”Enfermedad”); $D$1;E4-(D4-C4)-B4).

Esta complicada función SI da la siguiente instrucción a Excel: “Si, para una fecha en la columna F (“Ausencia”) se ha introducido “Vacaciones” o “Enfermedad”, las 8 horas de trabajo que debe el empleado de la columna D1 se han de registrar como horas efectivas trabajadas (con “Home office” no ha de ocurrir). Si, en cambio, la columna F está vacía, las horas de trabajo se computan con normalidad siguiendo esta fórmula: E4 (Final jornada) – (D4(Final pausa) – C4(Inicio pausa)) – B4 (Inicio jornada). Como intuyes, puedes incorporar esta fórmula a los otros días de la semana laboral arrastrando el cuadradito inferior con el ratón. La fórmula $D$1 garantiza que siempre se tomen como referencia la misma jornada laboral (8 horas).

Ahora se han de sumar las horas trabajadas para obtener el número total de horas trabajadas a lo largo de la semana:

  1. En la primera celda de la columna G que corresponda a un sábado (en nuestro ejemplo, será la G9), escribe la siguiente fórmula: =SUMA(G4:G8)
     
  2. Opcionalmente, en la pestaña “Fórmulas” puedes pulsar en el botón “Autosuma” y seleccionar “Suma”. Excel suministra entonces la fórmula correcta.

Paso 5: Cómo calcular las horas extras en tu tabla Excel

Con un pequeño truco, lograrás programar a tu tabla Excel para que cuente, no solo las horas extraordinarias efectuadas por tus empleados, sino también las negativas:

  1. Formatea la columna H (“Más/Menos”) como [h]:mm:ss (utilizando aquí el mismo procedimiento que seguiste en la columna G (“Horas trabajadas”).
     
  2. Dado que Excel no puede representar por defecto ningún valor temporal negativo, tenemos que utilizar un pequeño truco en las “Opciones” de Excel. Clica en “Avanzadas” y busca en el grupo “Al calcular este libro” la entrada “Usar sistema de fechas 1904”. Marca la casilla y confirma los cambios con “Aceptar”.
     
  3. En nuestro ejemplo, en la celda H4 introducimos la siguiente fórmula: =G4-$D$1 (esta ordena a Excel que reste las horas efectivamente trabajadas a la jornada establecida).
     
  4. Incorpora esta fórmula al resto de días como ya sabes.
     
  5. Para sumar todas las horas positivas o negativas, puedes utilizar la función “Autosuma” o escribir la fórmula =SUMA(H4:H8).

Control de horarios en Excel: fórmulas básicas

En este apartado, repasamos las fórmulas que hemos utilizado y que necesitarás para crear una hoja de registro de horas trabajadas con Microsoft Excel. Si copias nuestro ejemplo 1:1, puedes utilizar estas mismas fórmulas sin cambios, pero si creas una hoja con un diseño propio, tendrás que adaptar los números de las celdas en las fórmulas. Para lograrlo, puedes orientarte por la captura de pantalla de arriba o por la plantilla descargable.

Objetivo Fórmula (en nuestra tabla)
Fecha actual =HOY()
Marcar el fin de semana en otro color =DIASEM($A1;2)>=6
Horas trabajadas por día =SI(O(F4="Vacaciones";F4="Enfermedad");$D$1;E4-(D4-C4)-B4)
Suma de horas trabajadas =SUMA(G4:G8)
Horas positivas y negativas por día =G4-$D$1
Suma de horas positivas y negativas =SUMA(H4:H8)

Control de los horarios de los trabajadores con Excel: puesta en práctica

Por último, explicaremos cómo puedes configurar tu plantilla para poder utilizarla en el largo plazo, protegerla parcialmente contra la escritura y comunicarla a tus empleados.

Reutilizar tu plantilla de registro de la jornada

El resultado de tu trabajo es una plantilla profesional que, por ahora, solo cubre una semana. Para ampliarla, puedes recurrir de nuevo a nuestro truco: marca con el ratón toda la semana, clica en el pequeño cuadrado de la esquina inferior derecha y, sin soltarlo, desplaza el ratón hacia abajo. Como puedes comprobar, Excel reconoce que se trata de un patrón recurrente y replica todos los formatos, menús desplegables, fórmulas y reglas para todas las fechas secuenciales. Esto es lo que te permitirá reutilizar tu plantilla tantas veces como sea necesario.

Proteger parcialmente tu plantilla contra escritura

Tu empleado ya puede registrar cuándo inicia y finaliza su jornada laboral, así como sus pausas, en las columnas B, C, D, E que configuraste para ello en el formato hh.mm (p. ej., 8:30) y también puede declarar sus vacaciones y sus días de baja laboral en la columna F. Pero, aparte de esto, también debe poder modificar el contenido y la estructura de la tabla. Para este propósito, se asignan a la tabla permisos parciales de escritura.

Procede como sigue:

  1. Si la hoja activa está protegida, todas las celdas tienen por defecto el estado “Bloqueado”. Para implementar una protección parcial, se ha de cambiar este ajuste.
     
  2. Marca la tabla por entero pulsando en el punto superior izquierdo donde se encuentran las columnas y las filas.
     
  3. Ahora, pulsa en “Inicio” en la pequeña flecha en la esquina inferior derecha del grupo “Alineación” para abrir la ventana “Formato de celdas”. Otra opción consiste en teclear [Ctrl] + [1].
     
  4. Ve a la pestaña “Proteger” y desmarca la casilla de “Bloqueada”.
     
  5. Confirma los cambios.
     
  6. Mantén pulsada la tecla [Ctrl] y marca con el ratón todas las celdas que quieres proteger. Te recomendamos que marques las filas 1, 2 y 3 y la columna A.
     
  7. Regresa a la pestaña “Proteger” y vuelve a marcar la opción “Bloqueada”.

Este método, sin embargo, no incluye las fórmulas en las columnas G y H. En este caso, sigue estos pasos:

  1. Teclea [Ctrl] + [I] para abrir la ventana “Ir a…”.
     
  2. Pulsa en el botón “Especial”.
     
  3. Marca el punto “Celdas con fórmulas” y confirma en “Aceptar”. Ahora, Excel marca todas las celdas que contienen fórmulas.
     
  4. Sigue los pasos descritos arriba para bloquear todas las fórmulas.

Pero, antes de enviar la hoja a tus empleados, has de activar la protección contra escritura:

  1. Clica en “Proteger hoja” en la pestaña “Revisar”.
     
  2. En “Permitir a los usuarios de esta hoja de cálculo”, desmarca la casilla de “Seleccionar celdas bloqueadas”.
     
  3. Tienes la opción de proteger la hoja con una contraseña.
     
  4. Clica en “Aceptar” (en caso de haber dado una contraseña, la debes confirmar de nuevo).

Ahora sí: envía tu hoja de trabajo en Excel a tus empleados

Guarda tu plantilla en el formato regular de Excel y envíala por correo a tus empleados, pero no olvides acompañarla de las instrucciones de uso para facilitar la comprensión de su funcionamiento: aclara, por ejemplo, a qué dirección electrónica y cuándo o en qué plazo de tiempo se ha de enviar la hoja. Indicando una forma unitaria de nombrar la hoja (p. ej., nombre, apellidos, fecha), es más fácil clasificar y organizar las hojas.

Consejo

Algunas alternativas a Excel también incluyen funciones para crear hojas de trabajo electrónicas.


¡No te vayas! ¡Tenemos algo para ti!
Consigue tu dominio .mx un año gratis.

Introduce el dominio que deseas en la barra de búsqueda para comprobar su disponibilidad.
12 meses desde $0/año
después $700/año