Échéancier de contrats d'assurance dans Excel : quelle date arrive
Un échéancier de renouvellement, c'est une ligne par contrat d'assurance que vous placez déjà. Vous saisissez le contrat, le client, la prime, la date de renouvellement, et le préavis en jours pour ce contrat. La date de préavis est la date de renouvellement moins ces jours. Les jours restants sont la date de renouvellement moins aujourd'hui tant que le contrat est encore actif ou en préavis. Une fenêtre dit echeance quand les jours restants sont dans le préavis que vous avez saisi, et plus-tard quand le renouvellement est encore plus loin. Le premier écran est une feuille nommée Contrats. La ligne 1 est l'en-tête. La ligne 2 est un contrat que vous pouvez contrôler à la main : un numéro, un client, une prime, un renouvellement 20 jours après aujourd'hui, et 30 jours de préavis. La date de préavis est 10 jours avant aujourd'hui. Les jours restants sont 20. La fenêtre est echeance, parce que 20 est dans 30. La prime est sur la ligne et aucune formule ne la lit. Cette feuille n'est pas le calendrier de baux d'un gestionnaire immobilier, ni la liste de ventes d'un agent immobilier, ni un agenda de visites, ni un calcul de commission. Un bail suit un lot. Le pipeline d'un agent suit des mandats qui ne deviendront peut-être jamais une vente. Ici le contrat d'assurance existe déjà. La question est quelle date de renouvellement est dans la fenêtre de préavis saisie sur cette ligne. Le délai de préavis est un nombre que vous saisissez sur la ligne, parce qu'il n'est pas le même pour chaque contrat. La feuille n'invente pas un délai légal. Si le contrat dit 90 jours, vous tapez 90. Si un autre dit 30, vous tapez 30. Vous devez lire le contrat. Les lignes termine et renouvele sortent de la fenêtre. Elles restent sur la feuille. Elyx peut écrire la date de préavis, les jours restants et la fenêtre. Vous saisissez toujours la date de renouvellement et les jours de préavis. L'essai dure 7 jours, sans carte.
Le probleme
La liste des renouvellements échoue quand elle vit dans un agenda. Une note dit qu'un contrat se renouvelle au printemps. Personne ne peut trier par date de renouvellement, et personne ne voit quelle ligne est déjà dans sa propre fenêtre de préavis. Une phrase sans date n'est pas un calendrier. Un second échec est de mélanger les contrats que vous placez avec des baux ou avec des biens que vous essayez de vendre. Une date de fin de bail répond à la question du gestionnaire immobilier. Un mandat et une visite répondent à la question de vente d'un agent. Si vous ajoutez ici des numéros de lot et des montants d'offre, le filtre du matin ne veut plus dire quel renouvellement d'assurance arrive. Gardez cette feuille pour les contrats, les dates de renouvellement, et les jours de préavis saisis. Un troisième échec est de supprimer un contrat quand il se renouvelle. Passez le statut à renouvele ou termine et laissez la ligne. La fenêtre devient vide. Supprimer la ligne retire la seule trace de la date que vous avez déjà passée.
Les benefices
Chaque contrat est une ligne, avec une date de renouvellement et un préavis en jours que vous avez saisi.
La date de préavis est la date de renouvellement moins ces jours.
Les jours restants sont la date de renouvellement moins aujourd'hui tant que le contrat est vivant.
Echeance veut dire que les jours restants sont dans le préavis de cette ligne.
Les lignes termine et renouvele restent sur la feuille et sortent du compte des échéances.
Tutoriel pas a pas
Créer la liste et les en-têtes
Nommez la feuille Contrats. En ligne 1 : Contrat, Client, Prime, Renouvellement, JoursPreavis, DatePreavis, Statut, JoursRestants, Fenetre, Note. Contrat est le numéro. Client est un nom. Prime est le montant que vous avez saisi. Renouvellement est une vraie date. JoursPreavis est un nombre entier saisi pour ce contrat. DatePreavis, JoursRestants et Fenetre sont des formules. Statut est un mot d'une liste. Note est facultative. Figez la ligne 1. Ne fusionnez pas les cellules. La prime est visible sur le premier écran et aucune formule ne la lit.
Une ligne par contrat en cours. Un renouvellement qui remplace la date doit mettre à jour Renouvellement, ou devenir une nouvelle ligne si vous avez besoin des deux dates. Ne gardez pas deux lignes actives pour un seul contrat.
Verrouiller les mots de statut
Sélectionnez Statut à partir de la ligne 2. Données, Validation des données, Liste. La source, sur une feuille nommée Listes, est actif, preavis, renouvele et termine. Actif veut dire que le contrat court et que le préavis de renouvellement n'est pas lancé. Preavis veut dire que le préavis a été envoyé ou reçu et que la date de renouvellement tient toujours. Renouvele veut dire qu'une nouvelle période est convenue : mettez cette date dans Renouvellement ou ouvrez une nouvelle ligne, et passez cette ligne à renouvele si c'est l'ancienne période. Termine veut dire que le contrat n'est plus sur cette période. Seuls actif et preavis restent dans la fenêtre d'échéance. Il faut choisir un mot. Un synonyme comme fini ou parti ne correspond pas.
Les jours de préavis ne sont pas un statut. C'est un nombre. Ne tapez pas 90 dans la colonne statut.
Saisir un contrat que vous pouvez contrôler à la main
En ligne 2, saisissez un contrat, un client, une prime, une date de renouvellement 20 jours après aujourd'hui, et 30 jours de préavis. Statut actif. Vous prévoirez la date de préavis à 10 jours avant aujourd'hui, les jours restants à 20, et la fenêtre à echeance, parce que 20 est dans 30. En ligne 3, un renouvellement dans 100 jours et 30 jours de préavis. Cette fenêtre doit être plus-tard. Utilisez des dates que vous pouvez compter sur un calendrier avant de faire confiance aux formules.
Si vous ne connaissez pas le délai de préavis, laissez JoursPreavis vide. La date de préavis et la fenêtre restent vides. N'inventez pas un nombre pour que la cellule ait l'air finie.
Écrire la date de préavis
La date de préavis est vide quand la date de renouvellement ou les jours de préavis sont vides. Sinon c'est la date de renouvellement moins les jours de préavis. Avec Renouvellement en D2 et JoursPreavis en E2, utilisez =SI(OU(D2="";E2="");"";D2-E2). Mettez la colonne au format Date. Si elle affiche un grand nombre, le format est Standard : passez-le en Date. Excel stocke les dates comme des numéros de jours, donc soustraire 30 à une date donne la date 30 jours plus tôt. C'est la date de préavis pour le nombre que vous avez saisi. Ce n'est pas un avis juridique et cela ne lit pas la prime.
=SI(OU(D2="";E2="");"";D2-E2)Un préavis de 0 veut dire que la date de préavis est égale à la date de renouvellement. Tapez 0 seulement si le contrat le dit.
Écrire les jours restants et la fenêtre
Les jours restants sont vides quand le statut est termine ou renouvele, ou quand la date de renouvellement est vide. Sinon ils sont la date de renouvellement moins aujourd'hui. Avec Statut en G2, utilisez =SI(OU(G2="termine";G2="renouvele";D2="");"";D2-AUJOURDHUI()). Mettez un format Nombre, pas Date. Un nombre négatif veut dire que la date de renouvellement est déjà passée et que la ligne est encore actif ou preavis. La fenêtre utilise le même test contre les jours de préavis : =SI(OU(G2="termine";G2="renouvele";D2="";E2="");"";SI(D2-AUJOURDHUI()<=E2;"echeance";"plus-tard")). Echeance veut dire que les jours restants sont inférieurs ou égaux aux jours de préavis de cette ligne. Plus-tard veut dire que le renouvellement est encore plus loin que ce préavis.
=SI(OU(G2="termine";G2="renouvele";D2="";E2="");"";SI(D2-AUJOURDHUI()<=E2;"echeance";"plus-tard"))AUJOURDHUI avance chaque matin. Une ligne qui était plus-tard peut devenir echeance sans que personne modifie la date de renouvellement.
Compter les lignes en échéance
Sur une feuille nommée Matin, les lignes en échéance sont =NB.SI.ENS(Contrats!I:I;"echeance"). Les lignes en échéance du client nommé en A2 sont =NB.SI.ENS(Contrats!B:B;A2;Contrats!I:I;"echeance"). Les lignes termine et renouvele sont dehors parce que leur fenêtre est vide. Une fenêtre vide n'est pas echeance. L'en-tête dit Fenetre, pas echeance. Écrivez la règle à côté du compte. Triez la feuille des contrats par Renouvellement, la plus ancienne d'abord, quand vous voulez les dates les plus proches en haut. Le compte n'a pas besoin de ce tri. La prime n'est pas dans le compte.
=NB.SI.ENS(Contrats!I:I;"echeance")Ne comptez pas les lignes visibles à la main après un filtre pour taper par-dessus la formule.
Lire un renouvellement passé comme un signal, pas comme une suppression
Si JoursRestants est négatif et que le statut est encore actif, la date de renouvellement est passée et la ligne n'a pas été marquée termine ou renouvele. C'est une ligne à ouvrir, pas une ligne à supprimer. Soit la date est fausse, soit le statut n'a pas été mis à jour. Corrigez la cellule qui est fausse. Un nombre de jours restants sur une ligne termine ne devrait pas arriver, parce que termine vide cette cellule. Si vous voyez un nombre sur une ligne termine, la formule a été écrasée.
Filtrez Fenetre sur echeance pour la liste d'appels. Filtrez Statut sur termine quand vous avez besoin de l'historique.
Contrôler trois lignes avant de partager le compte
Renouvellement dans 20 jours et préavis de 30, statut actif : jours restants 20, fenêtre echeance, date de préavis 10 jours avant aujourd'hui. Renouvellement dans 100 jours et préavis de 30, statut actif : fenêtre plus-tard, parce que 100 n'est pas dans 30. Le même renouvellement à 20 jours avec le statut termine : jours restants vides, fenêtre vide, et le compte des échéances ignore la ligne. Si un contrôle échoue, corrigez le format de date ou le mot de statut avant d'envoyer le compte. Le compte n'est que cette feuille. Il ne dit pas si un préavis a été signifié, et il ne tarifie pas la prime.
Écrivez les trois contrôles sur Matin si plusieurs personnes mettent les contrats à jour.
Fonctionnalites du template
Date de préavis à partir des jours saisis
Date de renouvellement moins les jours de préavis. Vide si l'une des deux saisies est vide.
=SI(OU(D2="";E2="");"";D2-E2)Jours restants tant que le contrat est vivant
Date de renouvellement moins aujourd'hui. Vide quand le statut est termine ou renouvele.
=SI(OU(G2="termine";G2="renouvele";D2="");"";D2-AUJOURDHUI())Fenêtre contre le préavis de la ligne
Echeance quand les jours restants sont dans le préavis. Plus-tard sinon. Vide quand le contrat est fermé.
=SI(OU(G2="termine";G2="renouvele";D2="";E2="");"";SI(D2-AUJOURDHUI()<=E2;"echeance";"plus-tard"))Compte des échéances par client
NB.SI.ENS lit le client et le mot de fenêtre. La prime n'est pas un argument.
=NB.SI.ENS(Contrats!B:B;A2;Contrats!I:I;"echeance")L'historique reste sur la feuille
Les lignes termine et renouvele restent. Elles sortent du compte parce que la fenêtre est vide.
Exemples concrets
Dans la fenêtre de préavis
Vous voulez savoir si un contrat qui se renouvelle dans 20 jours est déjà dans un préavis de 30 jours que vous avez saisi.
Renouvellement : 20 jours après aujourd'hui. Jours de préavis : 30. Statut : actif. Prime saisie et ignorée.
Resultat : La date de préavis est 10 jours avant aujourd'hui. Les jours restants sont 20. La fenêtre est echeance. Le compte inclut la ligne. Le 30 est le nombre saisi, pas une valeur par défaut.
Encore plus loin que le préavis
Un autre contrat se renouvelle dans 100 jours. Le préavis de ce contrat est 30 jours. Vous ne le voulez pas sur la liste du matin.
Renouvellement : 100 jours après aujourd'hui. Jours de préavis : 30. Statut : actif.
Resultat : Les jours restants sont 100. La fenêtre est plus-tard, parce que 100 est supérieur à 30. Passez les jours de préavis à 120 et la même date devient echeance, parce que 100 est dans 120.
Un contrat terminé sort de la liste
La période est finie. Vous le voulez hors du compte et encore visible.
Statut passé de actif à termine. Date de renouvellement inchangée.
Resultat : Les jours restants deviennent vides. La fenêtre devient vide. Le compte des échéances retire la ligne. La date de renouvellement est encore sur la feuille.
Astuces de pro
Un nombre de préavis par contrat
Ne mettez pas un seul délai dans une cellule de coin pour toutes les lignes, sauf si tous les contrats le partagent vraiment. Une colonne saisie par ligne survit au contrat qui est différent. S'ils partagent un nombre, vous pouvez pointer E2 vers cette cellule, et vous devez l'étiqueter pour qu'une modification ultérieure soit visible.
La prime n'est pas la fenêtre
La prime peut vivre dans sa colonne. Aucune formule ne la lit. La question du matin est la date de renouvellement. Mélanger un total de primes dans la formule de fenêtre fait dépendre le compte de l'argent au lieu des dates.
Une date en texte ne se soustrait pas
Si Renouvellement a été collé en texte, D2-E2 ne sera pas une date et D2-AUJOURDHUI() ne sera pas un nombre de jours. Saisissez à nouveau la date. Puis relisez la fenêtre.
Distinct des baux et des ventes immobilières
Les fins de bail sont sur le calendrier du gestionnaire immobilier. Mandats, visites et commissions sont sur les listes de l'agent immobilier. Ne collez pas un lot ou un acheteur dans Contrat.