Plantilla Excel para Gestionar Tiempos y Vacaciones

Plantilla Excel para Gestionar Tiempos y Vacaciones

Descubre la solución perfecta para la gestión de RRHH con nuestra plantilla Excel, diseñada para facilitar el control de las horas anuales y semanales, seguimiento de las vacaciones de los trabajadores, y la organización eficiente de los turnos de empleados.

Gestionar las vacaciones, las horas y los turnos de un equipo es una de esas tareas de recursos humanos tan necesarias como complicadas: evitar que se descuadren las horas, que ninguna semana supere el máximo permitido y que todo cuadre a final de año. En este tutorial vemos una plantilla completa de Excel que funciona de cero a 100 para controlar todo esto, cómo se configura, cómo se usa y, sobre todo, qué fórmulas se han empleado en cada parte. Lo más especial es que está construida íntegramente con funciones dinámicas (arrays): a partir de unos datos iniciales, la plantilla genera todo el resto de la información sin tablas dinámicas ni cálculos manuales.

Cómo está organizada la plantilla

Es una plantilla por persona: cada hoja gestiona las vacaciones, los turnos y los horarios de un único empleado. En el cuadro inicial cargas los datos de partida:

  • Nombre o referencia del empleado (código o lo que uses internamente).
  • Año que quieres cuadrar e inicio y fin de contrato. Si es indefinido, irá del 1 al 31; si la persona se incorpora a mitad de año o tiene contratos parciales, ajustas las fechas.
  • Tipo de vacaciones: naturales o laborables (si los días pedidos cuentan de lunes a viernes o periodos completos).
  • Días de vacaciones de este año y los acumulados del año anterior que quedaron pendientes.
  • Horas anuales a cumplir y horas semanales máximas permitidas.

Además de estos datos básicos, necesitas alimentar varias tablas auxiliares:

  • Listado de turnos: cuándo empieza cada turno (A, B, C…), cuántas horas tiene y qué libranzas lleva asociadas. Las libranzas se codifican por día de la semana (1 = lunes, 2 = martes… 6 = sábado, 7 = domingo). Puedes ir cambiándolas por tramos si alguna semana se trabaja el sábado y se libra el lunes.
  • Inicio y fin de vacaciones pedidas.
  • Festivos, tanto nacionales como locales.

El resultado: control automático

Con esa información, la plantilla muestra todas las semanas del año e indica si se superan o no los límites (por ejemplo, 40 horas semanales). También ves las vacaciones pedidas de forma acumulada, su límite y cómo se reparten por meses. Si en un tramo de turnos pones 10 horas diarias en lugar de 7,5, los indicadores reaccionan al instante y la tabla señala en rojo la semana que incumple. Y si añades una nueva petición de vacaciones (por ejemplo, del 1 al 10 de octubre), todo se recalcula automáticamente.

La pestaña Calendario: el motor de fórmulas

Todo el cálculo día a día vive en la pestaña calendario y se lee de izquierda a derecha, de forma secuencial.

1. Generar las fechas del contrato

Lo primero es generar todos los días en que el trabajador estará en la empresa con la función SECUENCIA:

  • Filas: fecha fin de contrato menos fecha inicio, más uno (para incluir ambos extremos).
  • Columnas: una sola.
  • Inicio: la fecha de inicio del contrato.
  • Paso: 1 (en Excel los días son unidades, así que sumar 1 avanza un día).

2. Semana, mes y día de la semana

Sobre esa columna de fechas se aplica BYROW para procesar fila a fila, referenciando el rango con A2# (el # lo interpreta como salida matricial). Cada BYROW lleva su LAMBDA con un único parámetro x, que representa cada fila:

  • Número de semana: NUM.DE.SEMANA sobre cada x (con criterio para indicar cuándo empieza la semana).
  • Mes: MES de cada fecha.
  • Día de la semana: DIASEM con criterio 2, que hace que la semana empiece en lunes.

Estos tres datos (semana, mes y día) se usan luego para calcular acumulados por semana, libranzas y vacaciones por mes.

3. Horas teóricas y libranzas

Las horas teóricas de cada jornada se recuperan con BUSCARX contra la tabla de turnos: busca la fecha y devuelve las horas. La clave está en el modo de coincidencia -1 (siguiente elemento menor): así, mientras no llegue el próximo tramo, devuelve las horas del tramo anterior. El mismo BUSCARX se usa para recuperar las libranzas.

Después se comprueba si el día es de libranza con COINCIDIR: devuelve error si no lo encuentra y posición si lo encuentra. Combinando con la lógica de error, se traduce a un 1 (sí trabaja) o un 0.

4. Días laborables reales con multiplicaciones

Se generan columnas con unos y ceros para libranzas y festivos (un 0 significa que ese día no se trabaja). Al multiplicar las columnas, basta con que una sea 0 para que el día deje de contar como laborable. Así se descuentan automáticamente libranzas y festivos.

5. Vacaciones con una LAMBDA recursiva

El tercer elemento son las vacaciones. Para ello se ha creado una función a medida, GENERADIASVACACIONES, que a partir de pares de fechas inicio/fin genera todos los días intermedios y los apila en una matriz. Es la parte más compleja del fichero: una LAMBDA recursiva que se llama a sí misma hasta procesar todas las filas, con una condición de parada cuando la ronda supera el número de filas. Internamente se apoya en SECUENCIA y APILAR.V.

Con esa matriz se hace un COINCIDIR para marcar qué días caen en vacaciones, usando ESNOD y la doble negación (--) para convertir el resultado en 0 (es vacación) o 1. Se generan tanto las vacaciones en días naturales como en días laborables (estas últimas descartan los días que ya son fin de semana o festivo).

6. Horas de trabajo efectivas

Finalmente, las horas reales se obtienen multiplicando las horas teóricas por las tres condiciones (festivo, libranza y vacaciones). Si alguna es 0, ese día no suma horas. También se añaden columnas con el turno y el nombre de la persona, útiles para futuras evoluciones de la plantilla.

Los resúmenes acumulados

Con todo calculado de forma secuencial, los resúmenes del inicio salen solos: suma de días de vacaciones según el tipo, suma de horas anuales y horas por semana. Para el resumen semanal se usa UNICOS para obtener las semanas distintas y luego se agregan las horas de cada una, equivalente a un agrupar por pero solo con funciones dinámicas: ni tablas dinámicas que actualizar ni nada. Lo mismo se hace por meses.

Conclusión

Esta plantilla demuestra cómo encadenar funciones dinámicas (SECUENCIA, BYROW, LAMBDA, BUSCARX, UNICOS, MAP…) para construir una herramienta sólida de control de horarios, turnos y vacaciones. Al estar estructurada paso a paso, resulta ideal para practicar el trabajo con rangos y arrays, que es lo que más cuesta dominar. Una solución automática y potente para que el control de horas y vacaciones deje de ser un quebradero de cabeza.

Más contenido de Excel en InflueXcel