Contar los días de contrato temporal por empresa en Excel

Contar los días de contrato temporal por empresa en Excel

La reforma laboral limita a 90 los días que una empresa puede tener a alguien contratado de forma temporal, y controlarlo a mano es un infierno cuando gestionas varias empresas a la vez.

En este tutorial montamos el cálculo desde cero a partir de un simple listado de contratos con fecha de inicio y fin: sacamos las empresas con UNICOS y TRANSPONER, generamos el calendario completo del año con SECUENCIA, y con CONTAR.SI.CONJUNTO averiguamos día a día cuántos contratos temporales tenía activos cada empresa. El resultado es un resumen mensual que te dice de un vistazo cuántos días ha consumido cada una.

El problema: 90 días y varias empresas que controlar

Cada cambio normativo es una oportunidad de oro para poner a prueba lo que sabes de Excel, y la reforma laboral dejó uno muy concreto: hay un límite de 90 días que una empresa puede tener a alguien contratado de forma temporal. Saber cuántos llevas consumidos es fácil si gestionas una empresa y dos contratos. Si llevas la gestión de varias a la vez, con contratos que se solapan y se encadenan, contar a mano no es viable.

El punto de partida es el listado que ya tienes: empresa, empleado, fecha de inicio y fecha de fin de cada contrato. Nada más. A partir de ahí montamos el cálculo completo en una hoja aparte.

Paso 1: saber con qué empresas trabajas

Lo primero es detectar cuántas empresas hay en el listado, sin escribirlas a mano. Para eso usamos UNICOS sobre la columna de empresa, que te devuelve cada una una sola vez.

El detalle importante es que lo envolvemos en TRANSPONER, porque nos interesa que salgan en fila, no en columna: van a ser las cabeceras de la matriz de cálculo. Si mañana entra una empresa nueva en el listado, aparece sola.

Paso 2: generar el calendario del año

Necesitamos una fila por cada día del año, porque la pregunta es día a día: ¿había ese día algún contrato temporal activo?

Aquí entra SECUENCIA. Recuerda que lo que hace es lanzar una secuencia de números indicando cuánto sumar en cada celda, y que en Excel las fechas son números: partimos del 1 de enero del año elegido y le vamos añadiendo un día, un día, un día. Al número de filas le metemos un condicional que comprueba si el año es bisiesto, para que salgan 365 o 366 según toque.

Con el año en una celda suelta, cambiarlo recalcula la hoja entera.

Al lado añadimos una columna con la función MES aplicada a cada fecha. No la usamos todavía, pero es la que hará posible el resumen mensual del final.

Paso 3: contar contratos activos día a día

Ya tenemos las empresas en las cabeceras y todas las fechas del año en las filas. Toca rellenar la matriz, y la función es CONTAR.SI.CONJUNTO con tres condiciones:

  • que la fecha de inicio del contrato sea menor o igual que la fecha que estamos evaluando;
  • que la fecha de fin sea mayor o igual que esa misma fecha —entre las dos, que el día caiga dentro del contrato—;
  • y que la empresa sea la de esa columna.

El resultado es una matriz que te dice, para cada día y cada empresa, cuántas personas había trabajando con contrato temporal.

Paso 4: pasar de personas a días

Y aquí está el matiz que decide si el cálculo sirve o no. Lo que la ley cuenta no es cuántas personas has tenido, sino cuántos días has tenido a alguien en temporal. Da igual que un día hubiera una persona o cuatro: ese día cuenta una vez.

Así que debajo de cada columna aplicamos CONTAR.SI con la condición de que el valor sea mayor que cero: al menos una persona ese día. Eso convierte la matriz de personas en el número de días consumidos por cada empresa.

Paso 5: el resumen mensual

Con la base montada, el resumen sale replicando la misma idea: un CONTAR.SI.CONJUNTO que pide dos cosas a la vez — que la columna de esa empresa sea mayor que cero, y que la columna del mes (fijada para todos los cálculos) coincida con el mes de la fila, 1, 2, 3, según corresponda.

Eso da la evolución mes a mes, que es lo que de verdad quieres ver: no solo el total, sino a qué ritmo se está consumiendo. Un gráfico al lado con el porcentaje sobre la disponibilidad anual y ya tienes de un vistazo cuánto margen le queda a cada empresa.

Por qué merece la pena montarlo

Este es uno de esos ejercicios que casi todo el mundo acaba necesitando en el día a día, y que se suele resolver a base de café y hoja aparte cada vez que alguien pregunta. Montado así, el cálculo se actualiza solo: añades contratos al listado, cambias el año, y el resumen se recalcula sin tocar una fórmula.

Más casos con estas funciones

Más contenido de Excel en InflueXcel