La gestión de empleados no es una tarea sencilla y menos aún cuando hablamos de cuadrar vacaciones. Es muy común que todos los trabajadores quieran coger sus días de vacaciones en las mismas fechas. Por esto, es muy útil llevar un registro de horas trabajadas, vacaciones, días de baja, etc. para poder controlar a toda la plantilla.

En el vídeo puedes ver paso a paso cómo crear tu plantilla desde cero. También puedes adquirir la plantilla totalmente operativa en este enlace.

Datos de la empresa

En la columna A puedes introducir los datos de tu empresa; la empresa, el centro, el año al que e hace referencia y el periodo que cubre el año.

Para obtener el periodo automáticamente usaremos la función FECHA para obtener la fecha inicial. En el primer argumento selecciona la celda que contiene el año y en los otros 2 argumentos escribe un 1 para obtener el primer día del año.

=FECHA(B5;1;1)

Igualmente, para obtener la fecha final del año utiliza la función FECHA. Esta vez en el segundo argumento introduce un 12, para indicar diciembre y en el tercer argumento introduce un 31 para obtener el último día.

=FECHA(B5;12;31)

Para identificar fácilmente las celdas que no deben ser modificadas puedes aplicar un color de relleno.

Datos de los trabajadores y resumen de horas trabajadas

Combina las celdas correspondientes de las columnas A y B para insertar el nombre de los trabajadores. Realiza la misma operación con las celdas correspondientes de las columnas C y D para insertar el departamento de cada trabajador.

Utiliza las siguientes 3 columnas para insertar el resumen de horas trabajadas, días de vacaciones disfrutados y días de baja. Aplica un color de relleno diferente, más adelante introduciremos las fórmulas en las celdas correspondientes.

Crea el primer mes de registro

Vamos a crear el primer mes de registro que será enero. Selecciona desde la columna H hasta la columna AL y reduce el tamaño de las columnas hasta obtener cuadrados aproximadamente.

En la fila 8 insertaremos el nombre del mes. En la fila 9 obtendremos el día de la semana y en la fila 10 cada uno de los días.

Obtener el día del mes

Utiliza la función VALOR.NUMERO para obtener el primer día del mes.

=VALOR.NUMERO(1&H8B5)

De esta forma obtendrás la fecha del primer día del mes. Como no podemos visualizarlo dentro de la celda se mostrará ##. Cambia el formato de la fecha para visualizar solo el día.

El siguiente día se obtiene fácilmente sumando a la celda anterior un día, tal y como se muestra en el vídeo. Arrastra la fórmula para obtener todos los días del mes.

Obtener el día de la semana automáticamente

Utiliza la función TEXTO. Esta función tiene únicamente 2 argumentos. En el primero selecciona la celda inferior que contiene el día y en el segundo indica el formato que quieres mostrar. En este caso “ddd” (tres d entre comillas) para indicar que queremos ver una abreviatura del día.

=TEXTO(H10;“ddd”)

Arrastra esta fórmula al resto de los días.

Resaltar fines de semana y no laborables

En la zona de datos insertaremos las horas que trabaja cada empleado por día. Si es un día de vacaciones introduciremos una V y si es un día de baja una B.

Usaremos el formato condicional para resaltar los sábados y domingos. Selecciona el rango donde quieres aplicar el formato y utiliza la siguiente fórmula:

=O(H&9=“sá”; H&9=“do”)

En una hoja auxiliar introduce todos los festivos tal y como se muestra en el vídeo.

De nuevo vamos a utilizar el formato condicional con la siguiente fórmula:

 =COINCIDIR(H$10;Aux!$A$2:$A$25;0)>0

Del mismo modo, usando el formato condicional resaltaremos los días que contengan una B o V que indican días de baja o vacaciones, usando la siguiente fórmula:

=H11=“V”

=H11=“B”

 Resumen de datos al final del mes

Utiliza las siguientes 3 columnas después del último día del mes para crear un resumen de datos.

Obtendremos las horas trabajadas sumando todas las horas de cada una de las filas.

En el caso de los días de vacaciones y los días de baja usaremos la función CONTAR.SI

=CONTAR.SI( H11:AL11;“V”)

=CONTAR.SI( H11:AL11;“B”)

Crea el resto de meses del año

Para crear el resto de meses solo tendremos que copiar el primer mes que hemos creado y configurado y pegarlo a continuación.

Como sabes, todos los meses tienen siempre el mismo número de días salvo febrero. Por lo que debes eliminar un día en los meses que tengan 30 días y en el mes de febrero cambiaremos la fórmula que calcula la fecha para obtener 28 o 29 días dependiendo de si es bisiesto o no.

A partir del segundo día utiliza la siguiente fórmula:

 =SI(AP10<FIN.MES($AP$8&$B$5;0);AP10+1;“”)

Arrastra esta fórmula desde el segundo día al 29 del mes de febrero. De esta forma, solo se mostrarán 29 días cuando el año sea bisiesto.

Resumen anual de horas trabajadas, días de baja y días de vacaciones

Cuando ya tengas todos los meses puedes introducir las fórmulas para crear el resumen anual de horas trabajadas, días de baja y días de vacaciones.

Solo tienes que sumar el resumen de cada uno de los meses.

Crear nuevo registro de año y limpiar plantilla

Si quieres mantener los registros de cada año solo tienes que realizar una copia de la hoja, cambiar el año en la celda B5 y borrar los datos que hubieras añadido en el año anterior.

En el vídeo se muestra cómo crear una macro sencilla para borrar los datos clicando en un botón.

Recuerda que al guardar tu plantilla debes hacerlo como un archivo habilitado para macros.

Acceso a la plantilla

Puedes adquirir la plantilla terminada totalmente funcional aquí.

Esta plantilla tiene creadas hojas para gestión hasta 2030, puedes seguir creado hojas de años posteriores fácilmente.

Incluye una hoja auxiliar «Aux» donde puedes introducir los festivos de tu país y los días no laborables en tu empresa. Así como un botón para poder limpiar todos los datos de la plantilla.