Control de Suscripciones: Fórmula para saber si están Activas, Próximas a Vencer o Vencidas

En la gestión diaria de cualquier negocio o incluso a nivel personal, llevar un control exhaustivo de las suscripciones, membresías o licencias es fundamental. Saber cuándo una suscripción está a punto de caducar permite tomar decisiones a tiempo, ya sea para renovarla, cancelarla o buscar alternativas. Afortunadamente, Microsoft Excel nos ofrece herramientas potentes para automatizar esta tarea y evitar sorpresas desagradables.

Esta entrada se centra en una fórmula versátil que combina varias funciones para clasificar el estado de una suscripción en tres categorías claras y directas: Activa, Por vencer y Vencida. Utilizando una fecha de inicio y la duración del contrato en meses, esta fórmula determinará automáticamente el estado actual, proporcionando un sistema de alerta visual y eficiente.

Sintaxis

=SI(FECHA.MES(fecha_inicio, duracion_meses) < HOY(), "Vencida", SI(FECHA.MES(fecha_inicio, duracion_meses) <= HOY() + 30, "Por vencer", "Activa"))

Esta fórmula anida dos funciones SI para realizar una serie de comprobaciones lógicas. El núcleo del cálculo es la función FECHA.MES, que determina la fecha de vencimiento, y la función HOY, que proporciona la fecha actual como punto de referencia. Veamos sus componentes:

• FECHA.MES(fecha_inicio, duracion_meses): Esta es la parte central. Calcula la fecha de vencimiento exacta sumando el número de meses de la duración (`duracion_meses`) a la `fecha_inicio`. Por ejemplo, si una suscripción anual comenzó el 15/05/2023, `FECHA.MES("15/05/2023", 12)` devolverá "15/05/2024".
• ... < HOY(): La primera comprobación lógica. Compara si la fecha de vencimiento calculada es anterior a la fecha actual. Si es cierto, significa que la suscripción ya ha caducado.
• "Vencida": Es el resultado que muestra la fórmula si la primera comprobación es verdadera.
• SI(...): Si la primera comprobación es falsa (la suscripción no ha vencido), la fórmula pasa a evaluar una segunda función SI anidada.
• ... <= HOY() + 30: La segunda comprobación lógica. Verifica si la fecha de vencimiento está dentro de los próximos 30 días (incluyendo el día de hoy). `HOY() + 30` calcula la fecha que será exactamente en 30 días.
• "Por vencer": Es el texto que se mostrará si la suscripción vence en los próximos 30 días.
• "Activa": Si ninguna de las dos condiciones anteriores se cumple, significa que a la suscripción aún le quedan más de 30 días de vigencia, por lo que se considera "Activa".

Ejemplos

Ejemplo 1 Gestión de un listado de suscripciones de software. Dada una tabla con el nombre del servicio, su fecha de inicio y la duración en meses, aplicaremos la fórmula para obtener el estado actual de cada una.

Para la celda D2, la fórmula sería:

=SI(FECHA.MES(B2, C2) < HOY(), "Vencida", SI(FECHA.MES(B2, C2) <= HOY() + 30, "Por vencer", "Activa"))

Suponiendo que la fecha de hoy es 20 de Mayo de 2024, los resultados serían:

• Celda D2: La suscripción vence el 15/06/2024. Está a menos de 30 días, por lo que el resultado es "Por vencer".
• Celda D3: Vence el 10/01/2025. Falta mucho, así que el resultado es "Activa".
• Celda D4: Venció el 01/08/2024. Suponiendo que la fecha actual es posterior, el resultado sería "Vencida". Si la fecha actual fuera anterior al 01/08/2024, estaría "Activa".

Ejemplo 2 Personalizar el período de aviso y los mensajes de estado. En lugar de un aviso de 30 días, queremos que nos alerte con 60 días de antelación y usar textos más descriptivos.

=SI(FECHA.MES(B2, C2) < HOY(), "CONTRATO EXPIRADO", SI(FECHA.MES(B2, C2) <= HOY() + 60, "RENOVAR PRONTO", "VIGENTE"))

Esta variante cambia el número de días de `30` a `60` y personaliza los textos de salida para que se ajusten mejor a la terminología de un departamento de compras, por ejemplo.

Ejemplo 3 Usar una celda de referencia para el período de aviso. Para hacer la fórmula más flexible, podemos almacenar el número de días de preaviso en una celda aparte (por ejemplo, F1). Esto nos permite cambiar el período de aviso para todas las suscripciones modificando una sola celda.

=SI(FECHA.MES(B2, C2) < HOY(), "Vencida", SI(FECHA.MES(B2, C2) <= HOY() + $F$1, "Por vencer", "Activa"))

En este caso, si en la celda F1 escribimos `45`, la fórmula alertará de las suscripciones que venzan en los próximos 45 días. El uso de referencias absolutas (`$F$1`) es crucial para que al arrastrar la fórmula hacia abajo, la referencia a la celda F1 no cambie.

Aplicaciones Prácticas

• 1Gestión de licencias de software: Ideal para departamentos de TI que necesitan controlar decenas o cientos de licencias de software y planificar sus renovaciones.
• 2Control de membresías: Muy útil para gimnasios, clubes, asociaciones o cualquier negocio basado en suscripciones para gestionar su base de clientes activos.
• 3Seguimiento de contratos y pólizas: Permite a empresas y particulares controlar la fecha de vencimiento de contratos de alquiler, pólizas de seguros, garantías de productos, etc.
• 4Recursos Humanos: Para llevar un registro de la caducidad de certificaciones profesionales o permisos de trabajo de los empleados.

Observaciones

Es importante destacar que la función HOY es una función "volátil". Esto significa que se recalcula cada vez que se abre el libro de Excel o se realiza un cambio en cualquier celda. Para este caso de uso, es exactamente el comportamiento deseado, ya que garantiza que el estado de las suscripciones esté siempre actualizado respecto a la fecha corriente.

Asegúrate de que los valores en la columna `fecha_inicio` sean fechas válidas reconocidas por Excel y que `duracion_meses` contenga únicamente valores numéricos.

Errores comunes

• #¡VALOR!: Este error aparecerá si la `fecha_inicio` no es una fecha válida (por ejemplo, es texto plano que parece una fecha) o si `duracion_meses` no es un valor numérico.
• #¿NOMBRE?: Ocurre si hay un error tipográfico en el nombre de alguna de las funciones, como escribir `FETCHA.MES` en lugar de `FECHA.MES`.

Alternativas

Para versiones más recientes de Excel (Excel 2019, Microsoft 365 y posteriores), se puede utilizar la función SI.CONJUNTO para evitar el anidamiento de funciones SI, lo que puede resultar en una fórmula más legible:

• =SI.CONJUNTO(FECHA.MES(B2, C2) < HOY(), "Vencida", FECHA.MES(B2, C2) <= HOY() + 30, "Por vencer", VERDADERO, "Activa")

  Esta fórmula evalúa las condiciones en orden. Si la primera es verdadera, devuelve "Vencida". Si no, pasa a la segunda. Si ninguna de las dos primeras se cumple, la condición `VERDADERO` actúa como un "comodín" para todos los demás casos, devolviendo "Activa".