Control de horas trabajadas en Excel

Si tienes que llevar el control de horas trabajadas en Excel de empleados, lo más práctico es que tengas una plantilla automatizada de registro de horas de trabajo. De esta forma, evitarás tener que crear cada mes una nueva hoja de registro. Vamos a armar paso a paso nuestro registro de jornada laboral en Excel.

Introduce los datos del control de horas trabajadas Excel

En una nueva hoja de Excel introduce en la parte superior el nombre que quieres darle a tu informe.

Hay algunos datos que tu informe debe contener como: nombre de la empresa, el centro, el mes a mostrar, el año y el periodo que comprende.

Introduce el nombre de la empresa, centro, mes y periodo cada uno en una línea. El año lo insertaremos en la misma fila que el mes, pero antes vamos a modificar el tamaño de las celdas que mostrarán los días.

Selecciona las columnas de la B a la AF, en total son 31 columnas, que corresponde con el número máximo de días que tiene un mes. Reduce la anchura de las columnas hasta que tengan una proporción más o menos cuadrada.

Ahora ya puedes combinar algunas celdas e introducir el campo año en la misma fila que el mes para que te quede más o menos como en la siguiente imagen.

Lista desplegable para los meses

En el campo meses vamos a insertar una lista desplegable. En una hoja nueva, escribe en una columna el primer mes del año y arrastra de la esquina inferior derecha de la celda para obtener el resto de meses, hasta diciembre.

Asigna un nombre al grupo de celdas que contiene los meses para que sea más sencillo crear la lista desplegable. Selecciona las celdas y en el cuadro de nombres introduce el texto “meses”.

De nuevo, en la hoja de tu informe, selecciona la celda donde debes insertar la lista desplegable. Ve a la pestaña Datos – Validación de datos.

En la ventana emergente, despliega en Permitir y elige la opción lista. En el campo Origen que se activa escribe =meses. Pulsa Aceptar para crear la lista desplegable.  

Ya tienes tu lista desplegable del control de horas trabajadas en Excel.

Automatizar el periodo del mes y año seleccionado

Para obtener automáticamente el periodo que comprende el mes y año seleccionado vamos a usar un par de funciones.

En la celda contigua al campo Periodo escribe el texto Del, seguidamente combina horizontalmente cuatro celdas. En esa celda combinada introduce la siguiente función =VALOR.NUMERO, abre paréntesis. Queremos obtener el primer día del mes y año seleccionado. Por lo que la función quedará así =VALOR.NUMERO(1&B5&H5)

Queremos obtener el primer día, junto al mes que se encuentra en la celda B5 y el año que se encuentra en la celda H5. Se mostrarán unos números, cambia el formato de la celda a fecha corta para que se muestre correctamente.

En la celda contigua escribe el texto al y seguidamente combina horizontalmente otras 4 celdas. En esta celda combinada obtendremos la fecha final usando la función FIN.MES

La función quedará así =FIN.MES(C6;0)

Donde C6 corresponde a la celda donde hemos obtenido la fecha inicial y el 0 indica que queremos la fecha final de ese mismo mes.

Crear tabla calendario automática de control de horas trabajadas

En la primera celda cuadrada de la fila 9 insertaremos el primer día del mes. Para obtener la referencia al primer día del mes y año seleccionado esta celda será igual a la celda C6 que es donde hemos calculado el primer día del mes, quedando así =C6

Verás que se muestran unos símbolos de almohadillas, ya que la fecha completa no entra en la celda cuadrada. Personaliza el formato de la fecha para mostrar solo el número.

Clica con el botón derecho en la celda y elige la opción Formato de celdas. En el menú izquierdo elige la opción Personalizada y en el campo Tipo escribe d

Pulsa Aceptar para aplicar el cambio. De esta forma, aunque la celda contiene la fecha completa, solo se mostrará el día.

Automatizar el resto de días del año

Queremos que los días del mes seleccionado se muestren de forma automática y correctamente. Para conseguirlo, vamos a usar la función SI. Como ya sabes, los meses pueden tener 28, 29, 30 o 31 días dependiendo del mes y el año, si es bisiesto o no.

En la celda correspondiente al segundo día escribe

=SI(B9<$H$6;B9+1;””)

Con esta función estamos diciendo a Excel que si la fecha del día anterior es inferior a la fecha final que se encuentra en la celda H6 debe sumar un día y en caso contrario debe dejar la celda en blanco.

Arrastra la función hasta la columna AF y cambia el formato para visualizar únicamente el día.

Obtener el día de la semana y total de horas

Para obtener de forma automática el día de la semana utiliza la función TEXTO.

Justo encima de la celda que contiene el 1 que corresponde al primer día introduce la siguiente función =TEXTO(B9;”ddd”)

Donde B9 es la celda que contiene el día, justo la celda inferior. Con el texto “ddd” estamos indicando a Excel que solamente muestre las dos primeras letras del día.

En la columna siguiente al último día de un mes de 31 días, calcularemos el total de horas trabajadas.

En primer lugar, introduce en la primera columna los nombres de los trabajadores y en la columna AG debes calcular la suma de horas correspondiente a cada trabajador.

Si has llegado hasta aquí ya tienes armada prácticamente tu plantilla de control de horas trabajadas en Excel. Ahora solo queda añadir algunos formatos condicionales.

Resaltar los fines de semana o días de la semana no laborables

Una automatización interesante es resaltar en un color diferente los días que no se trabaje. En el ejemplo usaremos los sábados y domingos, pero verás que es muy sencillo a cualquier día si en tu empresa tenéis otros días de descanso.

Selecciona todo el rango de celdas que quieres resaltar.

En la pestaña de Inicio, despliega en Formato condicional y selecciona Nueva regla. Selecciona la última opción del tipo de regla. Y escribe la siguiente fórmula =B$8=”do”

Con esta fórmula le está diciendo a Excel que aplique un formato diferente cuando el día de la semana sea domingo. Clica en el botón Formato y elige el formato que quieras usar.

Utiliza el mismo método para resaltar los sábados o cualquier otro día.

Resaltar festivos

Para resaltar los festivos crearemos un listado auxiliar en la hoja que hemos introducido el listado de meses.

También usaremos el formato condicional junto con la función COINCIDIR. Si quieres saber en detalle el funcionamiento de esta función te recomendamos visualiza el vídeo.

Selecciona de nuevo todas las celdas donde vas a aplicar el formato condicional. Despliega en Formato condicional y selecciona Nueva regla. Selecciona la última opción del tipo de regla.

Escribe la fórmula =COINCIDIR(B$9;Hoja2!$C$2:$C$13;0)>0

Donde Hoja2!$C$2:$C$13 hace referencia al listado de festivos introducidos.

Aplica el formato y pulsa aceptar.

Ten en cuenta que al cambiar de año también debes cambiar el año de los festivos si quieres que se marquen correctamente.

Plantilla para descargar

Aquí tienes la plantilla para descargar. Recuerda que en el tutorial puedes ver como personalizar la plantilla adaptándola a tus necesidades.

⚠ Esta plantilla es un recurso creado por Saber Programas y su uso está sujeto a derecho de autor y propiedad intelectual. No está permitido su uso con fines comerciales.