Seguimiento de ausencias multi‑mes en Employee Leave Tracker de Excel 2016

¿Tu plantilla de Employee Leave Tracker deja huecos cuando un permiso se extiende de un mes a otro? Descubre tres métodos –desde Power Query hasta una sola fórmula– para asignar correctamente cada día y obtener informes mensuales y anuales exactos en Excel 2016.

Índice

Contexto y problema recurrente

La plantilla “Employee Leave Tracker” incluida en Excel 2016 funciona muy bien mientras los registros de ausencia (Start Date / End Date) permanezcan dentro del mismo mes. El cálculo mensual se basa en una sencilla combinación de SUMIFS que compara el mes de inicio y de fin, de modo que:

Un registro 26 ene → 30 ene se contabiliza completo en enero (cinco días).
Un registro 26 ene → 04 feb solo aporta cero a enero y cero a febrero, porque ninguno de los dos extremos cae íntegramente dentro del mes en cuestión.

Ese “agujero” provoca informes subestimados, primas mal calculadas y, en el peor de los casos, errores de nómina.

Por qué la fórmula original `SUMIFS` se queda corta

SUMIFS compara registros fila a fila: si el mes(año) de Start Date coincide con el de la celda de resumen, suma la diferencia entre Start y End. Al cruzar meses, la comparación falla en ambos lados. Es un diseño rápido, pero no contempla la intersección parcial de fechas. Necesitamos un enfoque que:

Divida los rangos de fechas en días individuales o calcule la porción que pertenece a cada mes.
Escale sin romperse cuando la base crezca de unas decenas a miles de filas.
Sea manejable por usuarios que quizá no conozcan Power Query ni VBA.

Soluciones para distribuir ausencias multi‑mes

A. Power Query + Modelo de datos + Tabla dinámica

Convertir cada registro en una lista de fechas ofrece el máximo control, porque desnormalizamos la tabla (Start, End) a un “calendario de ausencias” día a día.

Pasos esenciales

Extraer la tabla de permisos a Power Query (Datos ► Obtener y transformar datos ► Desde tabla/rango).
Crear columna personalizada con la función = List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1, #duration(1,0,0,0)), que genera una lista de fechas consecutivas.
Expandir la lista (Convertir en filas) de modo que cada día ocupe su propia fila, manteniendo los campos Employee Name y Type of Leave.
Cargar al Modelo de datos, no solo a la hoja. Esto habilita el motor xVelocity y evita los 1.048.576 límites clásicos.
Insertar tabla dinámica conectada al Modelo de datos. En filas: Employee Name. En columnas: Year‑Month (de la fecha expandida, agrupando por año y mes). En valores: Recuento de Fecha o Suma de Días.

Ventajas

Escalable: Power Query+Power Pivot maneja millones de registros.
Multi‑vista: basta arrastrar campos para agrupar por semana, trimestre, tipo de ausencia o cualquier jerarquía de calendario.
Mantenimiento mínimo: al agregar nuevas filas a la tabla original, solo presionas Actualizar.

Inconvenientes

Requiere habilitar complementos y conexiones; algunos usuarios bloquean Power Query por políticas TI.
El archivo puede crecer de forma significativa (cada permiso de 365 días produce 365 filas nuevas).

B. Fórmula sin Power Query

Si el entorno impide usar consultas o si necesitas una solución “solo Excel”, una SUMPRODUCT avanzada calcula la intersección entre el período del permiso y el mes de referencia:

=SUMPRODUCT(
  (LeaveTracker[Employee Name]=$B$4) *
  (LeaveTracker[Type of Leave]='Leave Types'!$B$5) *
  (MAX(LeaveTracker[Start Date],EOMONTH($D$2,-1)+1) <= MIN(LeaveTracker[End Date],EOMONTH($D$2,0))) *
  ( MIN(LeaveTracker[End Date],EOMONTH($D$2,0))
  - MAX(LeaveTracker[Start Date],EOMONTH($D$2,-1)+1) + 1 )
)

Cómo funciona pieza a pieza

Elemento	Función
`$B$4`	Empleado sobre el que se hace el resumen.
`'Leave Types'!$B$5`	Tipo de ausencia filtrado (vacaciones, enfermedad, etc.).
`$D$2`	Cualquier fecha del mes a consolidar (ej. 01‑ene‑2025).
`EOMONTH($D$2,-1)+1`	Primer día del mes.
`EOMONTH($D$2,0)`	Último día del mes.
`MAX(Inicio, PrimerDía)`	Detecta la parte del permiso que cae dentro del mes.
`MIN(Fin, ÚltimoDía)`	Trunca el permiso si se extiende más allá del mes.
`+1`	Convierte la diferencia de fechas en número de días inclusivo.

Pros

Sin macros ni complementos: es “hoja pura”.
Copi‑pega rápido: basta con una celda auxiliar por mes.

Contras

Complejidad: la fórmula es intimidante y difícil de mantener.
Velocidad: SUMPRODUCT recorre toda la matriz; con miles de filas y docenas de meses puede ralentizarse.
Sin jerarquías dinámicas: para obtener totales anuales debes sumar 12 celdas o replicar la fórmula.

C. Dividir registros manualmente

El método “brutal” consiste en separar cada permiso que cruza un mes en dos o más filas, de forma que cada nueva línea quede confinada a un solo mes. Por ejemplo:

Registro original: 26 ene → 04 feb (10 días).
Tras dividir: 26 ene → 31 ene (6 días) y 01 feb → 04 feb (4 días).

Cuándo puede servir

Bases muy pequeñas (menos de 100 registros/mes).
Usuarios reacios a fórmulas complejas o a Power Query.
Necesidad de un archivo ultraligero (cada día cuenta).

Riesgos

Incrementa el trabajo manual y el riesgo de error.
Cualquier cambio posterior en fechas obliga a repartir otra vez.
Historial menos legible: un permiso de tres meses se muestra como tres filas “distintas”.

Comparativa de los tres enfoques

Criterio	Power Query	Fórmula	Manual
Escalabilidad	★★★★★ (hasta millones)	★★★☆☆	★☆☆☆☆
Mantenimiento	Automático (con actualizar)	Medio (edición de fórmulas)	Manual intensivo
Curva de aprendizaje	Media	Alta (fórmula compleja)	Baja
Informe dinámico (tabla pivote)	Sí, nativo	Requiere agregar tabla pivote aparte	No, salvo recálculo manual
Tamaño del archivo	Alto (una fila por día)	Moderado	Bajo

Buenas prácticas al configurar el libro

Incluye un calendario maestro en el Modelo de datos, así podrás relacionar cualquier tabla de transacciones con la misma dimensión de tiempo (año, trimestre, mes, semana ISO).
Protege la hoja que contiene fórmulas; evita que un borrado accidental rompa el cálculo.
Define nombres estructurados (LeaveTracker[Start Date], [End Date]) en lugar de referencias A2:A1000, para que las fórmulas sigan funcionando cuando añadas filas.
Optimiza seguridad + conexiones: después de descargar un ejemplo con Power Query, haz clic derecho ► Propiedades ► Desbloquear. Guarda en una ubicación de confianza y habilita contenido.
Comprueba la zona horaria: si los permisos pueden registrarse desde distintas delegaciones, controla que las fechas se importen como Fecha, no como Texto.
Valida datos al capturar: mediante Validación de datos impide que el End Date sea anterior al Start Date.

Implantación paso a paso (caso Power Query)

Para ilustrar la robustez del método A, sigue este guion con un archivo de ejemplo (puedes recrearlo en menos de 10 minutos):

Crea la tabla LeaveTracker con las columnas Employee Name, Start Date, End Date, Type of Leave. Marca Start/End como “Fecha, sin hora”.
Lanza Power Query y aplica los pasos descritos antes. Comprueba que la columna “Fecha” está en formato Date y que genera tantas filas como días totales.
Carga al Modelo y construye la tabla dinámica. Agrupa por “Años” y “Meses” si Excel no lo propone.
Crea un Segmentador de datos (Slicer) para Type of Leave; así podrás filtrar vacaciones, licencias médicas, etc. en un clic.
Guarda y comparte. El destinatario solo tendrá que pulsar “Actualizar todo” cuando lleguen nuevas ausencias.

Consejos de rendimiento

En Power Query, ordena pasos para que los más selectivos (Filtrar Columnas, Quitar Filas) aparezcan antes. Reducirás el tamaño de la vista previa y del Model.
En fórmulas, evita matrices enteras (A:A) dentro de SUMPRODUCT; usa rangos con nombre dinámico que se ajusten al número real de filas (OFFSET + COUNTA o una tabla estructurada).
No mezcles decenas de fórmulas volátiles (HOY(), DESREF) con SUMPRODUCT; dispara recálculos innecesarios.

Preguntas frecuentes

¿Puedo aplicar estos métodos en Excel 365?

Sí. La única diferencia es que Power Query viene activado por defecto y las tablas dinámicas del Modelo de datos pueden usarse en Power BI si deseas cuadros de mando más sofisticados.

¿Hay forma de hacerlo con VBA?

Claro. Un procedimiento que recorra cada registro y genere filas hijas día a día es trivial (bucle For…Next). Sin embargo, Power Query ofrece el mismo resultado sin mantenimiento de código ni advertencias de macros.

¿Cómo gestiono permisos de medio día?

Convierte tu columna de Start Date/End Date en DateTime y, en Power Query, calcula la duración exacta usando Duration.TotalHours. En la tabla dinámica, divide el recuento de horas entre 8 (o la jornada oficial) para obtener “días equivalentes”. En la fórmula B, sustituye +1 por +0.5 si detectas medio día.

Conclusión

No existe una “bala de plata” única, pero en la mayoría de escenarios corporativos el combo Power Query + Modelo de datos ofrece la mezcla óptima de precisión, velocidad y flexibilidad. Si trabajas en un entorno restrictivo o con bases reducidas, la fórmula B es tu aliada: se integra en tu plantilla actual sin activar complementos. Y, si el tamaño del equipo lo permite, separar manualmente los registros sigue siendo válido siempre que mantengas una disciplina férrea en la actualización.

Sea cual sea el camino, el objetivo es el mismo: que ningún día de ausencia se pierda entre los pliegues del calendario. Con estas técnicas, tu Employee Leave Tracker estará listo para reflejar con exactitud los permisos que abarcan varios meses, evitando sorpresas en nómina y proporcionando métricas fiables a Recursos Humanos.