Control de asistencia en Excel: plantilla y fórmulas

Un control de asistencia en Excel necesita una sola hoja de registro —una fila por persona y por día, con hora de entrada y de salida— y tres fórmulas: =(D2-C2)*24 para las horas trabajadas, un SI para la tardanza y SUMAR.SI.CONJUNTO para el resumen del mes. El único punto en el que casi todo el mundo se equivoca es el formato de hora, así que empezamos por ahí.

Lo primero: Excel no guarda horas, guarda fracciones de día

Para Excel, las 8:30 no son «ocho y media». Son 0,354166: la fracción del día que ha transcurrido. Un día entero es 1, medio día es 0,5.

De ahí salen los dos errores clásicos:

• Restar entrada y salida y obtener 0,38. Correcto: es la fracción del día. Multiplica por 24 y tienes 9,13 horas.
• Sumar las horas del mes y ver 5:24. También correcto, y también inútil: el formato de hora da la vuelta cada 24 horas. Necesitas el formato [h]:mm, con corchetes, que le dice a Excel que siga contando más allá de un día.

Para aplicarlo: selecciona el rango, Ctrl + 1, pestaña Número, categoría Personalizada, y escribe [h]:mm en el cuadro.

Con esas dos cosas claras, el resto es directo.

Paso 1: la hoja de registro

Una hoja Registro, una fila por persona y por día:

Columna	Campo	Ejemplo
A	Fecha	24/08/2026
B	Colaborador	Ana Quispe
C	Entrada	08:57
D	Salida	18:05
E	Horas	(fórmula)
F	Tardanza	(fórmula)
G	Observación	Permiso médico

Conviértela en tabla con Ctrl + T y pon validación de datos en la columna del colaborador, apuntando a una hoja Personal con la lista de nombres. Si los nombres se escriben a mano, «Ana Quispe» y «ana quispe» son dos personas distintas para SUMAR.SI, y el resumen del mes sale mal sin que nadie lo note.

Dos atajos que hacen que la gente sí llene el archivo:

• Ctrl + Shift + : escribe la hora actual en la celda.
• Ctrl + ; escribe la fecha de hoy.

Luis entró 09:22 con hora de ingreso a las 09:00: la fila se pinta sola y la tardanza queda en minutos, no en «llegó tarde».

Paso 2: las horas trabajadas

En la columna E:

=(D2-C2)*24

Da las horas en decimal (9,13), que es lo que necesitas para sumar, promediar y multiplicar por una tarifa. Si prefieres verlo como 9:08, quita el *24 y aplica el formato [h]:mm.

Si descuentas el refrigerio y lo registras en dos columnas más (salida y regreso del almuerzo):

=((D2-C2)-(F2-E2))*24

Si hay turnos que cruzan la medianoche:

=SI(D2<C2;D2+1-C2;D2-C2)*24

El SI detecta que la salida es «menor» que la entrada —06:00 es menor que 22:00— y le suma un día. Sin eso, la resta da negativo y Excel llena la celda de almohadillas.

Paso 3: tardanzas y horas extra

Pon la hora de ingreso pactada en una celda fija, por ejemplo $J$1 = 09:00. Nunca dentro de la fórmula: el día que cambie el horario de un área, tocas una celda.

Minutos de tardanza:

=MAX(0;(C2-$J$1)*24*60)

MAX(0; …) es lo que evita que llegar temprano cuente como tardanza negativa y compense una llegada tarde de otro día. Si tienes tolerancia de cinco minutos, la fórmula pasa a =MAX(0;(C2-$J$1-$J$2)*24*60) con la tolerancia en $J$2.

Horas extra, sobre una jornada de 8 horas:

=MAX(0;E2-8)

Aquí hay que ser honesto con lo que la hoja puede y no puede hacer: en muchos países las horas extra se pagan con recargos distintos según el tramo, el día de la semana o si es feriado, y a menudo requieren autorización previa. Excel calcula el número; el criterio de si esa hora se paga, y a cuánto, no lo pone la hoja. Habla con tu contador o con quien lleve la planilla antes de usar esta columna para pagar.

Semáforo: selecciona el rango de tardanza y aplica formato condicional con =$F2>0 en rojo. Una segunda regla con =$F2>15 en un rojo más fuerte separa «se le hizo tarde» de «esto es otra conversación».

Paso 4: el resumen del mes

En una hoja Resumen, una fila por colaborador. Con la primera fecha del mes en $G$1 y la última en $G$2:

Horas del mes:

=SUMAR.SI.CONJUNTO(Registro!$E:$E;Registro!$B:$B;$A2;Registro!$A:$A;">="&$G$1;Registro!$A:$A;"<="&$G$2)

Días trabajados:

=CONTAR.SI.CONJUNTO(Registro!$B:$B;$A2;Registro!$A:$A;">="&$G$1;Registro!$A:$A;"<="&$G$2)

Veces que llegó tarde:

=CONTAR.SI.CONJUNTO(Registro!$B:$B;$A2;Registro!$F:$F;">0";Registro!$A:$A;">="&$G$1)

Siete tardanzas en el mes no es un problema de puntualidad de un día: es un dato para una conversación.

Cambiar las dos fechas de $G$1 y $G$2 recalcula el cuadro entero. Es lo que convierte el archivo en algo que se usa cada mes en lugar de rehacerse cada mes.

Todo esto funciona mientras alguien teclee las horas. Y ahí está el problema: quien llega tarde es quien anota su propia hora de entrada. No es una cuestión de confianza, es de diseño: un registro que la persona registrada puede editar sin dejar rastro no prueba nada, ni ante ella misma ni ante una inspección. Así lo resuelve Zentral.

Lo legal: lo que la hoja no cubre

En varios países de la región el registro de jornada es obligatorio y tiene requisitos formales: qué datos debe contener, cuántos años hay que conservarlo, si necesita firma o autenticación de quien marca, y en algunos casos si debe llevarse por medios electrónicos con trazabilidad.

Un .xlsx en una carpeta compartida cumple lo primero y falla lo último: cualquiera con acceso puede cambiar una hora de la semana pasada y no queda rastro. Antes de dar por resuelto el tema, revisa con un asesor laboral qué exige la norma de tu país para tu tamaño de empresa. Es de las pocas cosas del blog donde equivocarse tiene multa.

Cuándo deja de alcanzar

• Cuando la gente no está en una oficina. Personal en obra, en ruta o en varias sedes no va a llenar una hoja compartida.
• Cuando las horas alimentan la planilla. Copiar totales de un Excel a otro para calcular sueldos es el punto donde un error de tecleo se convierte en un pago mal hecho.
• Cuando hay turnos rotativos, francos o feriados. Las reglas se multiplican y las fórmulas dejan de caber en una celda.
• Cuando necesitas que el registro sea prueba. Si tiene que servir ante un tercero, necesita autenticación y un historial que nadie pueda reescribir.

Resumen

1. Excel guarda las horas como fracción de día: multiplica por 24 o usa el formato [h]:mm.
2. Una hoja de registro, una fila por persona y día, con los nombres validados desde una lista.
3. Horas: =(D2-C2)*24. Con medianoche de por medio: =SI(D2<C2;D2+1-C2;D2-C2)*24.
4. Tardanza: =MAX(0;(C2-$J$1)*24*60), con la hora pactada en una celda aparte.
5. Resumen con SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO entre dos fechas.
6. Lo legal se consulta, no se supone.

Si ya llevas los gastos y la caja en Excel, este archivo cierra el tercer frente: el costo de personal, que suele ser el más grande de todos.