Excel : calculer des dates et des échéances pour alerter

Vous devez calculer des dates pour suivre des délais d’échéance et agir en cas de retard. Sous Excel, plusieurs fonctionnalités et formules peuvent vous aider : la fonctionnalité Mise en forme conditionnelle, les formules aujourdhui(), =si(estvide(), =SI, =NB.SI, =NBVAL et l’option Format personnalisé. Prêt/e ? GO !

Activ’Formation | Excel | Calculer des dates


1. Découvrir l’exemple et préparer le fichier

Pour visualiser les retards de paiement et effectuer les rappels à bon escient, plusieurs fonctions et fonctionnalités d’Excel sont réunies dans un fichier simple à mettre en œuvre. Cet exemple s’adapte à tout type de fichier de contrôle de dates avec échéances : suivi de factures, relance de dossiers, suivi de contrats à renouveler à dates précises, etc.

  1. Le contenu des colonnes A, B, C et E est saisi manuellement (il pourrait résulter d’une fonction RECHERCHEV pour les données visibles en colonnes A, B et C).
  2. Le contenu de la colonne D repose sur la date saisie en colonne C : date facture +30 jours.
  3. Le contenu de la colonne E est associé à une Mise en forme conditionnelle (cellule colorée si elle est vide).
  4. Le contenu de la colonne F détermine de jour(s) de retard. Il est associé à une formule conditionnelle =SI( associée à ESTVIDE() et AUJOURDHUI().
  5. Le contenu de la colonne F est associé à un Format personnalisé. Il affiche « jour » ou « jours » selon le nombre de jour(s) de retard.
  6. Le contenu de la colonne G est associé à une formule conditionnelle =SI imbriquée (SI, SI).
  7. La cellule A12 détermine le nombre de factures émises : fonction =NBVAL.
  8. La cellule F12 détermine le nombre de factures en retard : fonction =NB.SI.
  9. La cellule G12 détermine le nombre de factures à relancer : fonction =NB.SI.

Pour mettre en œuvre cet exemple, ouvrir un fichier Excel contenant des dates, ou reproduire le contenu des cellules A1 à C11.

calculer des dates  Excel suivi des factures

2. Déterminer un délai de paiement de 30 jours

Saisir, dans la cellule de destination (ex. en D2) la formule =Date_fact+30 (ex. : =C2+30). Valider la formule et double-cliquer sur la poignée de recopie de la cellule en question (ex. : carré situé à l’angle inférieur droit de la cellule D2). La formule s’applique à toutes les cellules (ici : de D3 à D11).

Calculer des dates Excel ajouter 30 jours a une date

3. Visualiser, par une couleur, les factures non réglées

Sélectionner les cellules concernées (ici : E2:E11). Activer, sur le ruban d’Excel, l’onglet ACCUEIL. Repérer le groupe Style. Cliquer sur l’icône Mise en forme conditionnelle puis sur l’option Nouvelle règle.

Dans la boîte de dialogue Nouvelle règle de mise en forme qui s’affiche, cliquer sur Appliquer une mise en forme uniquement aux cellules qui contiennent. Dans la zone Modifier la description de la règle, activer la flèche de choix de l’option Appliquer une mise en forme uniquement aux cellules contenant puis choisir Cellules vides.

À droite de la zone Aperçu, cliquer sur le bouton Format et choisir un Remplissage. Valider en cliquant sur le bouton OK. Les cellules vides sont colorées. Le remplissage disparaît à la saisie d’une date.

Calculer des dates Excel mise en forme conditionnelle

4. Déterminer le nombre de jour(s) de retard.

Vous souhaitez afficher dans une ou des cellules (ici, de F2 à F11) le nombre de jours de retard par rapport à la date d’ouverture de votre fichier (aujourd’hui) ?

Saisir la formule suivante dans la première cellule de destination =SI(ESTVIDE(E2);AUJOURDHUI()-D2;0) et la recopier vers le bas (double-clic sur la poignée de recopie).

Lecture de la formule : SI le contenu de la cellule E2 (paiement) ESTVIDE ALORS AUJOURDHUI() moins D2 (Date limite de paiement) SINON 0.

En savoir plus sur les fonction =SI et =SI(ESTVIDE ? Suivre le lien !

Calculer des dates Excel retard paiement

5. Associer un texte à des cellules (ex. : retard x jour)

Sélectionner les cellules de réception (ici : F2:F11), cliquer sur le bouton droit de votre souris puis sur l’option Format de cellule. Dans l’onglet Nombre (actif par défaut) et dans la zone Catégorie, cliquer sur Personnalisée. Dans le volet droit et la zone Type, saisir la syntaxe :
[=1]“Retard ”0“ jour”;[>1]“Retard ”# ##0“ jours”;

Elle accorde « jour » en nombre avec le contenu de la cellule associée (1 jour, 2 jours…). Cliquer sur le bouton OK pour valider le Format personnalisé.

Calculer des dates Excel format personnalise

6. Relancer la facture si le retard est supérieur à X jours…

Sélectionner les cellules de réception (ici :G2:G11) et saisir la formule
=SI(F2=0;“ ”;SI(F2>5;“Relancer”;“ ”))

Lecture : SI le contenu de la cellule F2 (retard) est égal à 0 (zéro) ALORS « (vide) » ; SI le contenu de la cellule F2 (retard) est supérieur à 5 (jours) ALORS « Relancer » SINON « (vide) ».

Précisions :

• 0 est un nombre, la syntaxe  »  » matérialise un vide ;

• dans la formule, =SI(F2=0;“ ”;SI(F2>5;“Relancer”;“ ”)) la syntaxe =SI(F2=0 ;“ ”; exclut les cellules vides. La formule =SI(ESTVIDE(F2);“ ”; aurait pu la remplacer.

Calculer des dates Excel relance conditionnelle

7. Compter des « mots » avec =NBVAL

Se placer dans la cellule de destination (ici A12) et saisir la formule =NBVAL(A2:A11).

Elle compte les cellules non vides dans la plage sélectionnée (ici A2:A11).

Calculer des dates Excel compte nombre d'elements

8. Compter le nombre de factures en retard

Se placer dans la cellule de destination (ici F12) et saisir la formule =NB.SI(F2:F11;“>0”)

Lecture : calcule le nombre si, dans la plage de cellules observées, la valeur est supérieure à zéro.

Calculer des dates Excel nombre de factures en retard

9. Compter le nombre de factures à relancer

Se placer dans la cellule de destination (ici G12) et saisir la formule =NB.SI(F2:F11;“Relancer”)

Lecture : calcule le nombre si, dans la plage de cellules observées, la valeur est “Relancer”.

Calculer des dates Excel nombre de factures a relancer

Merci à Sandra F. de Paris, pour sa question sur le calcul des dates et les formules utiles pour suivre les relances  😉