Formato de control de asistencia de personal en Excel: guía práctica

Llevar la asistencia del personal en una hoja de Excel es una de las tareas más repetidas en oficinas, tiendas y pequeños negocios: una tabla con los empleados en filas y los días del mes en columnas, donde se anotan las marcas de entrada y salida a mano o con una fórmula. En este tutorial construyes esa hoja desde cero en un libro real, con ocho empleados, fechas de octubre 2026, formato de hora que aguanta más de 24 horas y un código de colores por letra (A de ausencia, T de tardanza, P de permiso). Al terminar tendrás una hoja rellenable cada mes, con totales automáticos y bloqueada para que nadie descoloque la estructura. Funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016; en versiones más antiguas la estructura es la misma, aunque algunas reglas de formato condicional tardan más en aplicarse.

Qué es un formato de control de asistencia y qué debe llevar

Una hoja de asistencia del personal es una tabla donde cada fila representa un empleado y cada columna un día del mes, de manera que cada celda guarda las marcas horarias de la jornada. La forma más extendida reserva dos marcas por día: la hora de entrada y la hora de salida, que se teclean en dos celdas contiguas o separadas por un guion dentro de una misma celda. Al final de la fila se calculan las horas trabajadas del día y, al final de la columna, los minutos de tardanza acumulados.

El esqueleto debe incluir los datos del empleado (nombre y, si hace falta, departamento o identificador), el calendario del mes con la fecha real en cada columna, las marcas horarias y una fila de totales por debajo del último empleado. Una columna extra con letras (A, T, P) acelera el rellenado manual y, combinada con formato condicional, sirve para ver de un vistazo quién faltó, quién llegó tarde y quién tuvo permiso, sin repasar las horas una por una.

Partes fijas de la hoja: empleado, fecha, día, entrada y salida

La distribución que se usa en este tutorial se apoya en un solo rango de fechas en la fila 2 (B2:H2), nombres de empleados en la columna A (A4:A11) y dos columnas por día: una para la hora de entrada (columna par) y otra para la hora de salida (columna impar a partir de la quinta). En la práctica simplificamos las dos marcas en una sola columna por día, con formato de hora que pintará tanto la entrada como la salida en cada celda.

La fila 1 agrupa los días por etiqueta, la fila 2 lleva el número de semana y la fila 4 (la que usa la fórmula de la siguiente sección) carga las fechas reales del mes en formato largo. Las celdas de datos, de B5 a H12 en el libro terminado, reciben números (la hora) o letras (A, T, P). Sobre esta misma estructura se pintan después los totales, el formato condicional y la protección.

El formato de celda que pinta la hora como la ves

Las horas en Excel son números decimales (1 representa un día entero, 0,5 son doce horas), así que el formato de celda es lo único que decide si ves 0,3333333333 o 08:00. El cuadro se abre en cualquier momento con Ctrl+1 y se elige la categoría Personalizada para escribir el código a mano.

Para los turnos que suman más de 24 horas en un mes hace falta el código [h]:mm, donde los corchetes le dicen a Excel que pinte las horas acumuladas en vez de reiniciar el contador cada día. Si la suma da 26:30 con [h]:mm, con h:mm se vería 2:30, exactamente el mismo valor numérico pero pintado de otra forma.

Las fechas largas (jueves, 1 de octubre de 2026) usan otro código, también escrito a mano en Personalizada:

dddd d "de" mmmm "de" aaaa

Una hoja de asistencia lista para rellenar en 10 minutos

El plan se divide en tres pasos claros: primero la estructura con nombres y fechas, después las reglas de color por letra y al final las fórmulas de horas trabajadas y minutos de tardanza. Cada paso deja la hoja lista para el siguiente, de modo que al terminar tienes una plantilla que solo hay que copiar cada mes con una fecha nueva en B2.

Los encabezados, los nombres y el rango de fechas

1. Abre un libro nuevo y guárdalo como Asistencia.xlsx.
2. Escribe en A1 Empleado, en B1 1, en C1 2, y así hasta H1 7; son los números de día que se convertirán en fechas reales con el formato de la sección anterior.
3. En A4 escribe Ana Soto, A5 Luis Cano, A6 María Pino, A7 Pedro Vera, A8 Sara Ruiz, A9 Diego Mora, A10 Nora Lago y A11 Iván Solà.
4. En B4 escribe la fecha 1/10/2026, selecciónala y arrastra el controlador de relleno hasta H4.
5. Con B4:H4 todavía seleccionado, abre Ctrl+1, elige Personalizada y escribe el código largo.

El código de colores para ver quién faltó de un vistazo

El formato condicional pinta la celda según la letra que escribas, sin que tengas que repasarla.

1. Selecciona B5:H11.
2. Abre la ruta Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es igual a.
3. En el cuadro escribe A, elige Relleno rojo y pulsa Aceptar. Repite la regla con T y Relleno amarillo, y con P y Relleno verde.

Las horas trabajadas y los minutos de tardanza por día

En este libro las horas se anotan en una sola celda por día con el formato hh:mm-hh:mm, pero para que la fórmula reste sola hace falta una columna extra con la hora de entrada y otra con la de salida. Como la práctica mantiene una sola columna por día, las horas trabajadas se calculan en una columna aparte a la derecha (I) usando GRANDE para sacar la mayor (salida) y la menor (entrada) del texto escrito.

1. En I4 escribe Horas, en J4 escribe Tardanza (min).
2. Selecciona I5, abre Ctrl+1 y aplica el formato [h]:mm.
3. En I5 escribe la fórmula que resta salida menos entrada parseando el texto:

=SI.ERROR(VALOR(EXTRAE(B5;ENCONTRAR("-";B5)+1;5))-VALOR(EXTRAE(B5;1;ENCONTRAR("-";B5)-1));0)

1. En J5 cuenta los minutos por encima de las 08:00 de la hora de entrada:

=SI.ERROR(SI(VALOR(EXTRAE(B5;1;ENCONTRAR("-";B5)-1))<=TIEMPO(8;0;0);0;VALOR(EXTRAE(B5;1;ENCONTRAR("-";B5)-1))-TIEMPO(8;0;0))*1440;0)

Totales del mes por empleado con una sola fórmula

Los totales mensuales se calculan al final de cada columna de día y al final de la fila por empleado. La fila de totales en B23:S23 resume cada jornada, mientras que en la fila del empleado aparecen las horas totales del mes, los días completos, las faltas, las tardanzas y los permisos.

Los valores numéricos se acumulan con SUMA y la columna de horas se multiplica por 24 para pintar un número decimal con dos cifras, fácil de sumar en otras hojas. Los recuentos de letras usan CONTAR.SI con el criterio exacto o con un comodín.

Cuántas faltas, cuántas tardanzas y cuántos permisos

Al final del rango por empleado (columnas T a X) se montan cuatro contadores con una sola función cada uno.

1. En T5 escribe =SUMA(B5:H5)*24 y aplica formato 0,00 " h" para ver 64,57 h.
2. En U5 cuenta los días con marca completa (entrada y salida escritas, ningún código de letra):

=CONTAR.SI(B5:H5;">0")

1. En V5 las faltas:

=CONTAR.SI(B5:H5;"A")

1. En W5 las tardanzas:

=CONTAR.SI(B5:H5;"T")

1. En X5 los permisos:

=CONTAR.SI(B5:H5;"P*")

Bloquear la hoja para que nadie cambie la estructura

Antes de imprimir o compartir la hoja, conviene proteger los encabezados y las fórmulas para que un dedo despistado no borre una fila o rompa un formato condicional. Excel permite dejar un rango editable dentro de una hoja protegida.

1. Selecciona B5:H11 y abre Revisar > Cambios > Permitir que los usuarios modifiquen rangos.
2. Pulsa Nuevo, escribe el nombre Marcas, deja la contraseña vacía y en Refiere a escribe =Asistencia!$B$5:$H$11.
3. Acepta, vuelve al cuadro principal y pulsa Proteger hoja.
4. Escribe una contraseña y confírmala.

Por qué los totales salen mal o se descolocan

Tres tropiezos típicos al montar esta hoja son el formato de hora, el controlador de relleno y el criterio de CONTAR.SI. Los tres tienen arreglo sin tocar la fórmula de fondo.

La suma da 2:30 cuando debería dar 26:30

El valor numérico es 1,104166… (veintiséis horas y media). El problema solo está en cómo lo pinta Excel.

Abre Ctrl+1, elige Personalizada y sustituye el código h:mm por [h]:mm. La celda sigue representando 1,104166… pero ahora se ve 26:30. La suma de las horas del día (I5 a I12) que parecía 17:30 también sube a 41:30 con el nuevo formato.

El controlador de relleno repite el mismo día 30 veces

Si arrastras B4 a la derecha y aparece 01/01/1900 o 1 repetido, la celda no tenía formato de fecha cuando la arrastraste. Selecciona B4:H4, abre Ctrl+1, elige Fecha y vuelve a arrastrar. Otra opción, más limpia, es escribir la fecha como fórmula y arrastrar:

=FECHA(2026;10;COLUMNA()-1)

De este modo cada columna suma un día a la anterior sin perder el formato.

=CONTAR.SI cuenta los permisos como si fueran faltas

CONTAR.SI(B5:H5;"P") no encuentra nada si en la celda escribes Permiso en vez de P. Cambia el criterio por "P*" con asterisco para que Excel considere cualquier texto que empiece por P. La doble comilla y el asterisco no se escriben como caracteres literarios: "P*" significa literalmente "empieza por P".

Lo que Excel no hace por ti en este formato

Excel no resuelve sin ayuda tres tareas habituales en recursos humanos. Turnos nocturnos que cruzan medianoche: una resta directa da negativo (por ejemplo, 02:00 - 22:00 es -20:00, no 4:00). Hay que sumar un día completo a la salida o usar MOD(salida-entrada;1). Festivos y días libres: el calendario mensual no se rellena solo; las fechas rojas de cada país hay que escribirlas a mano o traerlas con Power Query desde un calendario público. Acumulación de tres tardanzas seguidas como aviso: Excel no lleva un motor de reglas; necesitas una columna extra con SI(W5>=3;"Aviso";"") o una regla de formato condicional que detecte tres celdas amarillas consecutivas en la fila.

Lo que ya sabes hacer con tu hoja de asistencia

• Armar la estructura con nombres en A y fechas reales en B4:H4 arrastrando el controlador de relleno.
• Pintar la hora con [h]:mm para que la suma del mes pase de 24 horas sin reiniciarse.
• Aplicar formato condicional por letra (A rojo, T amarillo, P verde) con tres reglas rápidas desde Inicio > Estilos > Formato condicional.
• Proteger la hoja dejando solo B5:H11 editable para que nadie descoloque los encabezados ni las fórmulas de totales.

El siguiente paso natural es configurar el área de impresión con saltos de página manuales (Diseño de página > Ajustar área de impresión) para que el reporte mensual salga con los totales en una hoja aparte y los días completos en horizontal.

Dudas al usar un formato de control de asistencia en Excel

• ¿Cómo cambio el mes sin rehacer la hoja de asistencia?
• ¿Por qué la suma de horas muestra 2:30 en vez de 26:30?
• ¿Cómo bloqueo la hoja y dejo solo las celdas de marcas editables?
• ¿Qué fórmula uso para contar faltas, tardanzas y permisos?
• ¿Cómo calculo turnos nocturnos que cruzan medianoche?

Dudas al usar un formato de control de asistencia en Excel