Comment calculer automatiquement les dates d’échéances en fonction des dates d’émission de factures sur Excel

Dans ce nouveau tutoriel de la formation sur les dates et les heures, nous allons voir comment calculer automatiquement les dates d’échéances en fonction des dates d’émission de factures sur Excel. Pour cela, nous allons étudier les différentes conditions qu’une entreprise peut mettre en place (règlement comptant, à 30 jours, 45 jours fin de mois, le 15 du mois suivant,…) et nous serons amené à utiliser des fonctions spécifiques de manipulation des dates (notamment les fonctions FIN.MOIS() et DECALER.MOIS()).

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Règlement Comptant

Pour illustrer ce tutoriel, nous allons utiliser le document suivant :

Ce document reprend les différentes factures émises par une entreprise au cours de l’année 2019 qui n’ont toujours pas été réglées à ce jours.

L’objectif va donc être de déterminer de manière automatique les dates d’échéances de chacune de ces factures afin d’être alerté des retards éventuels dans les règlements.

Pour commencer, nous allons voir le cas le plus simple, il s’agit du règlement comptant.

En d’autres termes le paiement de la facture doit ici intervenir le jour même auquel la facture a été émise.

Pour calculer l’échéance, il suffit donc d’utiliser le symbole égal :

 =[@Date] 

Note : Comme vous pouvez le constater sur la capture, nous utilisons ici un tableau de données afin de grandement simplifier la rédaction des formules :

• Tout d’abord les références aux cellules sont directement appelées de manière claire : [@Date] signifiant ici que nous appelons la cellule qui se trouve à l’intersection de la ligne sur laquelle la formule est saisie et de la colonne qui porte le nom de Date (ce qui est plus parlant que C9 par exemple),
• Ensuite, il suffit de saisir une formule sur l’une des cellules de la colonne pour que celle-ci soit reportée automatiquement sur les cellules de la colonne

Pour tout savoir sur les tableaux de données, je vous invite à consulter ce tutoriel complet.

2. Règlement 30 jours

Pour ajouter 30 jours à une date, il suffirait en principe de procéder à une simple addition :

 =[@Date]+30 

En effet, comme nous l’avons déjà abordé à de nombreuse reprise dans les chapitres précédent de cette formation, une date pour Excel est en réalité un simple nombre dont le point de départ est le 1^er janvier 1900, auquel Excel ajoute une mise en forme particulière :

Mais en réalité, lorsqu’il est question de règlement à 30 jours, celui doit intervenir un mois pile après la date d’émission de la facture.

Sur la première ligne de l’exemple, celui-ci devrait donc être fixé au 11/08/2019 et non au 10/08/2019, le mois de juillet étant composé de 31 jours.

Pour calculer le jour auquel la facture devra effectivement être réglée, nous pouvons utiliser les formes DATE(), ANNEE(), MOIS() et JOUR() de la manière suivante :

 =DATE(ANNEE([@Date]);MOIS([@Date])+1;JOUR([@Date])) 

Nous cherchons ici à reconstituer la date qui correspond :

• À la même année,
• Au mois suivant (d’où le +1 pour effectuer un décalage d’un mois)
• Au même jour

Que la date d’émission de la facture

Même si cette formule est très simple à comprendre, elle nécessite d’appeler de nombreuses fois la formule contenant la date de facture, ce qui demande un peu de temps.

Heureusement, une fois de plus Excel va nous permettre de gagner du temps grâce à une formule toute faite dédiée à cette usage, il s’agit de la formule MOIS.DECALER() qui permet de récupérer une date qui se situe dans le passé ou dans le future par apport à une date de référence.

Celle-ci admet deux arguments :

• Date_Départ : il s’agit de la date de référence,
• Mois : il s’agit du décalage à appliquer à la date de départ en nombre de mois (Positif pour un décalage vers le futur, négatif pour un décalage vers le passé)

 =MOIS.DECALER([@Date];1) 

Ce qui est effectivement beaucoup plus rapide à saisir !

3. Règlement 30 jours fin de mois

La règle du règlement à 30 jours fin de mois prévoit que celui-ci soit opéré au plus tard à la fin du mois qui suit le mois d’établissement de la facture.

Encore une fois, il est possible de calculer cette date de manière manuelle, en déterminant la date du premier jour qui suit le second mois après l’établissement de la facture, auquel il ne suffit ensuite que de retirer une journée :

 =DATE(ANNEE([@Date]);MOIS([@Date])+2;1)-1 

Mais pour ce cas de figure aussi les ingénieurs de chez Microsoft ont prévu une petite formule qui va grandement nous simplifier la vie, il s’agit de la formule FIN.MOIS() :

 =FIN.MOIS([@Date];1) 

Comme pour la formule DECALER.MOIS() que nous avons vu juste avant, celle-ci admet deux arguments Date_Départ et Mois, la différence étant ici que la formule FIN.MOIS() retourne le dernier jour du mois correspondant :

4. Règlement 45 jours fin de mois

Pour le règlement dit à 45 jours fin de mois, il s’agit ici d’effectuer un premier décalage de 45 jours, puis prendre le dernier jour du mois correspondant.

Ici, nous allons à nouveau utiliser la formule FIN.MOIS(), mais que nous allons utiliser d’une manière un peu différente de l’exemple étudié juste au dessus :

 =FIN.MOIS([@Date]+45;0) 

En effet, nous commençons par ajouter les 45 jours à la date de départ, puis demandons le dernier jour du mois d’arrivé (soit un décalage de zéro mois) :

5. Règlement le 15 du mois suivant

En revanche, pour calculer une échéance qui interviendrait le 15 du mois suivant, il n’existe pas de formule toute prête.

Nous allons alors soit reconstituer la date correspondante :

 =DATE(ANNEE(C9);MOIS(C9)+1;15) 

Soit ajouter 15 jours à la fin du mois en cours :

 =FIN.MOIS([@Date];0)+15 

6. Règlement à 45 jours Fin de mois ou 60 jours

Pour ce dernier cas, la règle est un petit peu plus complexe, en effet celle-ci prévoit que le règlement intervient au plus tôt :

• Soit à 45 jours fin de mois,
• Soit à 60 jours (c’est-à-dire deux mois après l’émission de la facture)

Nous allons donc imbriquer ces deux formules dans une formule MIN() qui permet de récupérer la plus petite des valeurs saisies en arguments :

 =MIN(MOIS.DECALER([@Date];2);FIN.MOIS([@Date]+45;0)) 

Nous retrouvons dans cette formule les deux fonctions que nous avons découvertes précédemment :

• Pour obtenir un décalage de deux mois : MOIS.DECALER([@Date];2),
• Pour obtenir la date à 45 jours fin de mois : FIN.MOIS([@Date]+45;0)