Control de vacaciones en Excel: plantilla con fórmulas

Imagina un equipo de ocho personas y la pregunta que se repite cada lunes: quién está fuera hoy, cuántos días le quedan a cada uno y si habrá conflictos si dos del mismo departamento coinciden fuera. Un control de vacaciones en Excel es, sencillamente, un libro con esa información: una hoja por empleado con sus periodos y sus días, un calendario visual de todo el año y una hoja de totales calculados con fórmulas. Al terminar este tutorial tendrás un libro funcional con esos tres bloques, fórmulas que cuentan días naturales y laborables, formato condicional para pintar las ausencias, listas desplegables para evitar errores y la hoja de totales protegida para que nadie la rompa sin querer.

Funciona en Excel para Microsoft 365, 2024, 2021, 2019, 2016 y 2013, además de en Excel para la web, Mac y móviles. Algunas funciones como DIAS.LAB.INTL existen desde Excel 2010; las matrices dinámicas y LET son de 365/2021 en adelante; en este tutorial usamos solo funciones presentes en todas las versiones mencionadas.

Qué es un control de vacaciones en Excel y para qué sirve

Un control de vacaciones en Excel es un libro que registra, para cada persona de un equipo, qué días del año está fuera, cuántos le corresponden en total, cuántos ha gastado ya y cuántos le quedan por disfrutar. No es una base de datos ni un programa de gestión de personal: es una hoja de cálculo con datos bien ordenados y unas fórmulas que hacen las cuentas.

Sirve para responder preguntas concretas del día a día: ¿a Ana le quedan días?, ¿quién está fuera la semana del 15 de julio?, ¿podemos permitir que Carlos y Laura del mismo departamento coincidan en agosto? Con la plantilla lista, las respuestas salen de mirar una sola hoja en lugar de cruzar correos, WhatsApps y notas a mano.

En este tutorial vas a montar esa plantilla para un equipo de ocho personas, con una hoja de totales anuales, un calendario visual de todo el año y la posibilidad de añadir empleados nuevos sin tener que rehacer las fórmulas.

Las piezas que tiene toda plantilla de vacaciones: empleados, periodos, totales y festivos

Una plantilla de vacaciones bien armada tiene cuatro bloques claros, cada uno en su hoja o rango. Antes de escribir la primera fórmula, conviene tenerlos localizados, porque las fórmulas saltarán de uno a otro.

Esta separación hace que las fórmulas sean limpias: los totales suman los periodos, el calendario lee los festivos para no contar sábados y domingos, y las validaciones viven en las celdas donde el usuario escribe a mano.

Montar la tabla por empleado: departamento, días totales, usados y pendientes

Empezamos por la hoja Empleados, la más importante del libro. En ella vive la información de cada persona y las fórmulas principales. Abre un libro en blanco y crea la hoja siguiendo estos pasos:

1. En la celda A1 escribe Empleados y en A2 el año de trabajo, por ejemplo Año 2026.
2. En la fila 3 escribe las cabeceras, una por columna, en este orden: Empleado, Departamento, Días Totales, Días Usados, Días Pendientes, Aprobado.
3. En las filas 4 a 6 copia los datos de ejemplo.

La columna Días Pendientes se calcula sola. Selecciónala, escribe la fórmula y arrástrala hacia abajo con el controlador de relleno (el cuadradito de la esquina inferior derecha de la celda seleccionada) hasta la última fila de empleados.

=C4-D4

Las dos filas de periodos por persona: Inicio, Fin y Días calculados

A la derecha de la tabla principal reservamos dos filas por empleado para apuntar los periodos de vacaciones que ha pedido. Cada fila tiene fecha de inicio, fecha de fin y los días que dura, calculados con DIAS (en inglés DAYS).

La fórmula en I4 es:

=DIAS(H4;G4)+1

Se copia a I5 y, cuando haya más empleados, hacia abajo por todo el rango. El +1 es importante: si las fechas son la misma, DIAS devuelve 0, y un periodo de un día debe contar como 1, no como 0.

Sumar los dos periodos y calcular pendientes con una sola fórmula

Ahora conectamos los periodos con la columna Días Usados. En D4 sumamos los días de las dos filas de ese empleado; en E4 calculamos los pendientes totales.

=I4+M4

=C4-D4

Al copiar =C4-D4 hacia abajo (a D5, D6…) las referencias se mueven con la celda, así que no hace falta escribirlas a mano para cada empleado. Si algún día añades un tercer periodo en una tercera fila, sustituye la fórmula por =I4+K4+M4 y amplía el rango arrastrando.

Contar solo días laborables con DIAS.LAB y la lista de festivos

Hasta ahora hemos contado días naturales, pero las vacaciones se disfrutan en días laborables: ni sábados, ni domingos, ni los festivos del año. Para eso existe DIAS.LAB.INTL (en inglés NETWORKDAYS.INTL), disponible desde Excel 2010, que admite una lista de festivos como argumento y permite elegir qué días de la semana son fin de semana.

Crea una hoja nueva llamada Festivos y lista ahí los festivos del año. Después selecciona el rango de fechas y crea un nombre con el Cuadro de nombres (el cuadro a la izquierda de la barra de fórmulas) escribiendo Festivos_2026 y pulsando Entrar. Así podrás usar ese nombre en cualquier fórmula.

Ahora sustituimos la fórmula de I4 en la hoja Empleados por esta:

=DIAS.LAB.INTL(G4;H4;1;Festivos_2026)

El tercer argumento, 1, indica que el fin de semana es sábado y domingo. El cuarto argumento, Festivos_2026, es el nombre del rango con los festivos. Si quieres contar solo de lunes a viernes pero con un festivo especial que cae en domingo, esa fecha seguirá descontándose porque la lista lo dice expresamente.

Crear el calendario anual con marcas de ausencia

La hoja Calendario 2026 es la vista visual: una fila por empleado y 365 columnas con los días del año. Cada celda lleva una letra que codifica el tipo de ausencia: V para vacaciones, M para medio día, E para enfermedad, T para trabajo desde casa. Los fines de semana aparecen en gris para que el calendario se lea de un vistazo.

1. Crea una hoja llamada Calendario 2026.
2. En la fila 1, a partir de la columna B, escribe las fechas del 1 de enero al 31 de diciembre de 2026 (365 días). Truco: escribe la primera fecha en B1, pon el cursor en B1 y arrastra el controlador de relleno hasta NJ1 manteniendo pulsada la tecla Ctrl para copiar con incremento de un día.
3. En la columna A, a partir de la fila 3, escribe los nombres de los empleados (los mismos que en Empleados).
4. En las celdas B3:NJ3 (Ana Ruiz), B4:NJ4 (Carlos Torres) y así, escribe la letra correspondiente en cada día de ausencia.

Tipos de ausencia con código, color y si cuentan como día completo o medio día

Antes de aplicar el formato condicional, conviene tener un cuadro con los códigos y su comportamiento. La regla es: el calendario usa letras para representar la ausencia, y una hoja auxiliar con CONTAR.SI traducirá cada letra a días consumidos.

Estos colores son los que aplicaremos al calendario con formato condicional.

Colorear las celdas con formato condicional según el código

Con el calendario escrito, vamos a pintar cada celda según la letra que contiene. Selecciona todo el rango de marcas, B3:NJ10 (ajusta la última fila a tu número de empleados), y aplica una regla por cada código.

1. Selecciona B3:NJ10.
2. Abre Inicio > Estilos > Formato condicional > Nueva regla.
3. Elige Aplicar formato a las celdas que contengan.
4. En Valor, escribe ="V" y pulsa Formato…, rellena con color azul, acepta.
5. Repite para ="MA" (ámbar), ="MP" (ámbar), ="E" (rojo) y ="T" (verde).

Validar fechas y evitar solapes entre compañeros del mismo departamento

Las fechas y los departamentos son dos fuentes clásicas de errores en un control de vacaciones. Para evitar fechas fuera del año o anteriores al día en que se empieza a trabajar, y para detectar a ojo los solapes entre personas del mismo departamento, vamos a usar validación de datos y una hoja auxiliar de ordenación.

Pasos para limitar las fechas al año 2026:

1. Selecciona G4:H8 (los Inicios y Fines de los periodos).
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Fecha, en Datos elige entre y rellena 01/01/2026 y 31/12/2026.
4. Pulsa Aceptar.

Para detectar solapes, crea la hoja Solapes con esta estructura:

1. En A1, Empleado; en B1, Departamento; en C1, Inicio; en D1, Fin.
2. Copia con Ctrl+C los datos de la hoja Empleados y pégalos en Solapes con Ctrl+Alt+V > Valores.
3. Selecciona la tabla y aplica Datos > Ordenar, primero por Departamento y después por Inicio.

Cuando dos personas del mismo departamento aparecen en filas consecutivas con periodos que se solapan, lo verás a simple vista: la fecha de fin de una es posterior a la fecha de inicio de la siguiente.

Una lista desplegable Sí/No para marcar los periodos aprobados

La columna Aprobado de la hoja Empleados indica si un periodo ya ha sido validado por el responsable. Para no escribirlo a mano cada vez (y que un «sI» con la i minúscula rompa las búsquedas), creamos una lista desplegable.

Pasos:

1. Selecciona F4:F8.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Lista y en Origen escribe Sí;No (con punto y coma, separador de la lista, no de argumentos).
4. Pulsa Aceptar.

Proteger las celdas con fórmulas y dejar editables solo las de entrada

Una vez que las cuentas funcionan, hay que evitar que alguien borre una fórmula por accidente. Excel protege la hoja de forma que solo se pueden editar las celdas desbloqueadas; por defecto, todas están bloqueadas, así que primero desbloqueamos las que el usuario debe tocar y después protegemos.

Pasos:

1. Selecciona las celdas editables: nombres, departamentos, días totales, fechas de inicio y fin, columna Aprobado y las marcas del calendario.
2. Abre Inicio > Celdas > Formato > Bloquear celda y desmarca la opción. En la nueva ventana, deja Bloqueada sin marcar.
3. Selecciona toda la hoja con Ctrl+E (o Ctrl+A), abre el mismo cuadro y vuelve a marcar Bloqueada. Ahora todas las celdas están bloqueadas excepto las del paso 1.
4. Abre Revisar > Proteger hoja, marca Proteger hoja y contenido de celdas bloqueadas, escribe una contraseña y pulsa Aceptar.

Errores típicos del control de vacaciones y cómo arreglar fórmulas?

Cinco fallos reales que aparecen a menudo cuando se monta un control de vacaciones en Excel, con la causa y el arreglo.

DIAS devuelve un día de menos porque no cuenta el último

=DIAS("01/01/2026";"03/01/2026") da 2, no 3: la cuenta la fórmula sólo cuenta los días intermedios. La causa es que DIAS resta dos fechas sin incluir la última. El arreglo es sumar 1 al resultado.

=DIAS(G4;H4)+1

El total de pendientes sale negativo y no debería

Si alguien ha escrito más días usados de los que le tocan, la columna de pendientes muestra números negativos (por ejemplo, -3). Para impedirlo, envolvemos la resta en MAX(0;...) (en inglés MAX):

=MAX(0;C4-D4)

Así, aunque los días usados sean 25 y los totales 22, la celda mostrará 0, no -3.

El calendario muestra marcas en sábados y domingos

Si las celdas se han coloreado a mano en lugar de con formato condicional, las marcas pueden aparecer en cualquier día. Hay que borrar el color manual y aplicar una regla adicional: la celda se colorea solo si la fecha cae en día laborable. La fórmula de formato condicional usa DIASEM (en inglés WEEKDAY) con tipo 2 (que devuelve 1 para lunes y 7 para domingo):

=Y(DIASEM(B$1;2)<6;B3<>"")

Esta fórmula pinta la celda solo cuando es día laborable (lunes a viernes) y la celda no está vacía.

#¿NOMBRE? al pegar la fórmula en otro equipo

Si al abrir el libro en otro ordenador aparece #¿NOMBRE? en las celdas con DIAS.LAB.INTL, lo más probable es que esa versión de Excel no la reconozca. Si el libro se va a compartir con usuarios de Excel 2007 o anterior, sustituye la función por DIAS.LAB (en inglés NETWORKDAYS), que sí existe desde 2007 y admite la lista de festivos como argumento:

=DIAS.LAB(G4;H4;Festivos_2026)

Lo que ya sabes hacer con un control de vacaciones en Excel

• Montar la tabla por empleado con sus días totales, usados y pendientes calculados con =C4-D4.
• Contar días naturales con DIAS o solo laborables con DIAS.LAB y DIAS.LAB.INTL.
• Pintar el calendario anual con cinco tipos de ausencia usando formato condicional.
• Validar fechas y crear listas desplegables Sí/No para los periodos aprobados.
• Proteger las celdas con fórmulas y dejar editables solo las que el usuario debe tocar.

El siguiente paso es convertir cada bloque en una Tabla de Excel con Ctrl+T: las fórmulas que ya tienes se copiarán automáticamente a las filas nuevas, así que cuando añadas un empleado solo tendrás que rellenar su fila y la plantilla seguirá funcionando sin tocar nada más.

Dudas al montar el control de vacaciones en Excel