Excel: formule DATEDIF pour calculer la différence entre deux dates

Cette fonction n'est pas répertoriée dans l'aide d'Excel, mais elle est très utile pour calculer l'âge des personnes nées ou les dates d'échéance.

Syntaxe : DATEDIF (Date1; Date2;Intervalle)

• Date 1 est la date de Départ

• Date 2 est la date de Fin

• Intervalle peut prendre différentes valeurs:
            "y"  : différence en années
            "m" : différence en mois
            "d"  : différence en jours
            "ym" : différence en mois, une fois les années soustraites
            "yd"  : différence en jours, une fois les années soustraites
             "md" : différence en jours, une fois les années et les mois soustraits

  à noter : Date2 doit toujours être supérieure ou égale à Date1

Dans cet exemple, nous allons utiliser la fonction DATEDIF pour trouver la durée de 3 chantiers de construction, chacun ayant des dates de début et de fin connues :

Pour le chantier 3, nous pouvons déduire qu'il a duré moins d'un an (2 mois ou 63 jours).

A cette occasion, nous remarquons que la fonction DATEDIF renvoie des valeurs entières. Si un projet a duré 2 mois, la formule DATEDIF(C32;D32 ; "y") ne renvoie pas 0.1667 années mais 0 : moins d'une année complète est une valeur entière aux yeux d'Excel !

Les autres intervalles disponibles de la fonction vous permettent d'obtenir le nombre de mois ou de jours restants après la soustraction des années, ou le nombre de mois et d'années soustraits :

Toujours pour notre chantier 3, nous avons obtenu zéro pour le nombre d’années, 2 mois ou 63 jours, ces deux résultats ne changent donc pas ; en revanche, l’intervalle « md » restant, une fois les années et les mois retranchés, est bien de 1 jour : 63 moins 2 mois de 31 jours reste 1.

Il e va de même pour le premier projet, qui a duré du début à la fin sur une période de 5 ans et 61 mois, soit 1886 jours. Le nombre de jours dans une durée aussi longue peut être moins significatif que s'il était exprimé en années et en mois - ce que nous sommes mieux à même d'imaginer sur la base de nos expériences personnelles passées avec des projets basés sur le temps.

En fonction des intervalles choisis DATEDIF permet de dire que ce chantier a duré en tout 5 ans 1 mois et 28 j

Ce résultat peut être présenté dans un tableau comme dans notre exemple, mais il peut également être utile de l'écrire textuellement. Pour ce faire, nous utilisons l'opérateur de concaténation : & (équivalent à la fonction concatener()).

Pour obtenir les années : =DATEDIF(C30;D30;"y") renvoie 5; la formule =DATEDIF(C30;D30;"y")&"ans" renvoie le texte 5 ans.

Le même raisonnement est applicable pour les mois et les jours. Pour écrire sous forme de texte le résultat 5 ans 1 mois 28 jours il suffit donc d’appliquer la formule suivante :

Formule :

Comment gérer le singulier/pluriel :

Appliquée au chantier 2 (soit en remplaçant dans la formule ci dessus la ligne 30 par la ligne 31) cette formule renvoie : 1 ans 1 mois 1 jours . Le pluriel de ans et jours est ici mal venu. 

• Afin que le résultat soit correct aussi bien au singulier qu’au pluriel, une solution serait de mettre les "s" entre parenthèses :

  La formule =DATEDIF(C31;D31;"y")&" an(s)" renvoie le texte 1 an(s)

• Une autre solution est de tester si le nombre d'années/jours sont supérieurs à 1:

  Par exemple, pour le nombre d’années : =SI(DATEDIF(C31;D31;"y")>1;" ans ";" an ") renvoie ans au pluriel si le nombre d'années est >1, an au singulier dans le cas contraire.
  Le raisonnement est le même pour le nombre de jours, et pour donner le résultat 1 an 1 mois 1 jour la formule devient

   

Formule:

Comment gérer également les valeurs nulles :

Appliquée au site 3, notre dernière formule donne 0 ans 2 mois 1 jour. Le dilemme singulier/pluriel a été résolu, mais les zéros ont maintenant une apparence bizarre.

Sur son excellent site, Laurent Longre (site aujourd'hui hébergé sur excelabo) met à notre disposition une formule qui, à l’aide d’un format personnalisé, de la fonction datedif() associée aux fonctions texte() et supprespace(), permet de résoudre à la fois le problème d'orthographe et celui des valeurs nulles.

Formule de Laurent Longre adaptée à notre exemple :
 Formule :

Une formule qui peut être utilisée pour prédire la durée de toute situation similaire donnera un résultat dans les 2 mois et 1 jour pour notre exemple.

Cette formule peut sembler complexe si vous êtes novice en matière d'Excel, mais il existe d'autres approches - utilisant un tableau intermédiaire et des formules plus longues - si vous préférez cette approche.

Par exemple, dans le tableau ci-dessous, trois formules ont été données pour les années/mois/jours gérant les valeurs nulles ainsi que le singulier/pluriel, et la dernière formule synthétise le tout :

Pour les années en colonne E:
Formule :

• Si le résultat de DATEDIF(C4 ;D4) est égal à zéro, cela signifie qu’il y a moins d’un an entre les deux dates, dans ce cas nous n’affichons pas le résultat et mettons un vide (deux guillemets)

• Si non nous faisons un second test : Si le résultat de DATEDIF(C4 ;D4) est égal à 1, alors nous affichons le résultat suivi de an au singulier soit DATEDIF(C4 ;D4)& " an"

• Dernier cas possible une fois que les deux premiers ont été traités, il y a plusieurs années séparant les deux dates, nous affichons donc le résultat mais suivi de ans au pluriel soit en formule DATEDIF(C4;D4)& " ans".

Pour les mois en colonne F:
Formule:

La formule est plus simple puisque mois est invariable, il suffit de tester si le nombre de mois est nul afin d’afficher soit vide (avec deux guillemets sans espace "") soit le résultat suivi de mois.

Pour les jours en colonne G:
Formule:

Même principe que pour les années, un premier test pour gérer les valeurs nulles, suivi d’un second pour écrire jour au singulier ou au pluriel.

La concaténation colonne H:  
Formule:

Le résultat des trois formules précédentes est agrégé et séparé par un espace afin que les résultats ne soient pas collés les uns aux autres ( =SUPPRESPACE(E4&F4&G4) donnerait 2 mois1 jour sans séparation entre mois et 1 ce qui est inesthétique).

Le tout est intégré dans la fonction SUPPRESPACE() afin de supprimer les espaces inutiles.
En effet, dans notre exemple n’ayant pas d’année complète la cellule E4 est vide puis suivie d’un espace avant d’inscrire le nombre de mois. Cet espace est inutile, la fonction SUPPRESPACE() permet de l’éliminer.

A noter:

• Lorsque les dates sont saisies directement dans la formule, les noter entre guillemets:
                Exemple : DATEDIF("13/03/1964";"31/12/2009";"y")

• DATEDIF() gère les années bissextiles.

Pour effectuer des calculs sur des âges, ancienneté du personnel, date de règlement des factures pour gérer des relances, il est souvent bien utile de connaître cette fonction DATEDIF.