DATEDIF, AUJOURDHUI() ou FRACTION.ANNEE : quelle formule choisir pour calculer l’ancienneté dans Excel ?

Pour calculer une ancienneté dans Excel, il faut partir de deux dates, une date de début et une date de référence, puis choisir la formule adaptée au résultat attendu. Selon le besoin, vous pouvez obtenir des années complètes, des mois, des jours ou un affichage complet du type 2 ans, 4 mois, 5 jours.

Dans un contexte RH, paie ou suivi de contrat, ce calcul sert à suivre une ancienneté, vérifier une éligibilité, préparer un entretien annuel ou contrôler une prime d’ancienneté. Voici les méthodes fiables à utiliser, avec les formules à copier et les erreurs de date à éviter.

Poser correctement les dates avant de calculer l’ancienneté

Un calcul d’ancienneté repose toujours sur le même principe, Excel mesure l’écart entre une date de début et une date de fin. La date de début peut être la date d’embauche en cellule A2, et la date de fin une date de sortie, une date de paie ou la date du jour en cellule B2.

Date fixe ou date dynamique : le choix qui change tout

Si vous voulez connaître l’ancienneté à une date précise, saisissez cette date dans une cellule, par exemple B2. C’est utile pour figer un calcul au dernier jour d’un mois de paie, à la fin d’un contrat ou à la date d’un audit RH.

Si vous voulez une ancienneté mise à jour automatiquement, utilisez AUJOURDHUI(). La formule prend la date système du jour et recalculera le résultat à chaque ouverture ou recalcul du fichier. Pour une date d’embauche en A2, la date de fin dynamique peut donc être intégrée directement dans la formule.

Vérifier que les cellules sont bien reconnues comme dates

Avant de corriger une formule, vérifiez le format. Une date saisie comme texte peut sembler correcte visuellement, mais produire une erreur ou un résultat incohérent. Dans Excel, une vraie date est une valeur numérique affichée sous forme de date. Appliquez un format de cellule de type Date, puis testez si la soustraction fonctionne avec une formule simple comme =B2-A2. Le résultat doit être un nombre de jours.

La date de référence transforme une simple ligne administrative en donnée exploitable. Tant qu’elle reste floue, le tableau reste descriptif. Dès qu’elle est stabilisée, chaque ancienneté devient comparable, filtrable et plus simple à contrôler, notamment pour repérer les seuils internes, anticiper un anniversaire d’ancienneté ou préparer un contrôle paie.

Utiliser DATEDIF pour obtenir années, mois ou jours complets

La fonction la plus pratique pour le calcul ancienneté Excel est DATEDIF. Elle calcule la différence entre deux dates selon une unité choisie. Sa syntaxe est simple :

=DATEDIF(date_début;date_fin;unité)

Avec une date d’embauche en A2 et une date de référence en B2, vous pouvez calculer l’ancienneté dans plusieurs unités. Les unités principales sont "Y" pour les années complètes, "M" pour les mois complets et "D" pour les jours.

Comprendre les unités YM, YD et MD

DATEDIF ne se limite pas aux années, mois et jours totaux. Elle permet aussi d’isoler les restes après une première unité. L’unité "YM" renvoie les mois restants après les années complètes. L’unité "YD" renvoie les jours restants sans tenir compte des années. L’unité "MD" renvoie les jours restants après les mois et années complètes.

Ces unités sont utiles pour obtenir une ancienneté lisible, mais elles demandent un contrôle attentif. Microsoft signale que DATEDIF peut produire des résultats incorrects dans certains scénarios, en particulier avec certaines combinaisons de dates et l’unité "MD". Pour un usage RH sensible, il vaut mieux tester la formule sur plusieurs cas connus avant de l’intégrer dans un fichier partagé.

Afficher une ancienneté complète en années, mois et jours

Dans la plupart des tableaux RH, un simple nombre d’années ne suffit pas. Une personne ayant 5 ans et 11 mois d’ancienneté ne se trouve pas dans la même situation qu’une personne ayant 5 ans et 1 mois. Pour afficher une durée complète, il faut combiner plusieurs DATEDIF dans une formule concaténée.

La formule prête à copier

Si la date d’embauche est en A2 et la date de référence en B2, utilisez :

=DATEDIF(A2;B2;"Y")&" ans, "&DATEDIF(A2;B2;"YM")&" mois, "&DATEDIF(A2;B2;"MD")&" jours"

Le résultat s’affiche sous forme textuelle, par exemple 2 ans, 4 mois, 5 jours. C’est plus lisible dans un état RH, une extraction pour manager ou un tableau de suivi, mais ce n’est plus une valeur numérique directement exploitable pour des calculs. Si vous devez filtrer, sommer ou déclencher des règles, gardez aussi une colonne numérique en années ou en mois.

Version dynamique avec la date du jour

Pour éviter de saisir une date de référence dans chaque ligne, remplacez B2 par AUJOURDHUI() :

=DATEDIF(A2;AUJOURDHUI();"Y")&" ans, "&DATEDIF(A2;AUJOURDHUI();"YM")&" mois, "&DATEDIF(A2;AUJOURDHUI();"MD")&" jours"

Cette formule convient pour un tableau vivant d’ancienneté employé. En revanche, elle n’est pas adaptée si vous devez conserver une photo exacte à une date passée, par exemple pour une paie clôturée. Dans ce cas, utilisez une cellule de référence unique, par exemple E1, et verrouillez-la dans la formule avec une référence absolue : $E$1.

Exemple : =DATEDIF(A2;$E$1;"Y"). Vous pourrez ainsi modifier la date de référence une seule fois pour recalculer tout le tableau.

Choisir une alternative à DATEDIF selon le besoin

DATEDIF est pratique, mais ce n’est pas la seule solution. Pour certains usages, une formule plus simple ou plus stable peut suffire, notamment si vous voulez une valeur décimale, un nombre de jours ou un calcul de jours ouvrés.

FRACTION.ANNEE pour une ancienneté décimale

La fonction FRACTION.ANNEE calcule une durée en années décimales. Avec une date de début en A2 et une date de fin en B2 :

=FRACTION.ANNEE(A2;B2)

Le résultat peut être affiché avec 2 décimales, par exemple 5,75 années. Cette approche est intéressante pour des analyses, des tableaux de bord ou des comparaisons rapides. Elle est moins lisible pour un courrier RH ou un document salarié, car elle ne détaille pas les mois et jours restants.

Soustraction simple pour obtenir des jours

Excel sait soustraire deux dates directement. La formule =B2-A2 renvoie le nombre de jours calendaires entre les deux dates. Pour convertir ce résultat en semaines, vous pouvez diviser par 7 jours avec =(B2-A2)/7. Cette méthode est utile pour des durées simples, mais elle ne remplace pas un calcul complet d’ancienneté en années, mois et jours.

Jours ouvrés avec NETWORKDAYS.INTL

Si votre besoin porte sur des jours travaillés plutôt que sur une ancienneté calendaire, utilisez NETWORKDAYS.INTL. Cette fonction permet de calculer des jours ouvrés et de tenir compte d’un rythme hebdomadaire ou de jours fériés. Elle répond à un autre besoin, mesurer du temps ouvré, pas l’ancienneté contractuelle classique.

Fiabiliser un tableau RH et éviter les erreurs courantes

Un calcul d’ancienneté devient sensible dès qu’il sert à prendre une décision. La formule doit donc être protégée contre les dates manquantes, les inversions de dates et les cas métier particuliers.

Gérer les cellules vides et les erreurs

Si une date d’embauche manque, DATEDIF peut renvoyer une erreur ou un résultat trompeur. Vous pouvez entourer la formule avec SIERREUR pour afficher un message clair :

=SIERREUR(DATEDIF(A2;AUJOURDHUI();"Y");"Date à vérifier")

Pour éviter de masquer un vrai problème, ne remplacez pas toutes les erreurs par une cellule vide sans contrôle. Dans un fichier RH, il vaut mieux afficher Date à vérifier, puis corriger la donnée à la source.

Distinguer ancienneté brute, nette et périodes particulières

L’ancienneté brute correspond à l’écart entre l’entrée et la date de référence. L’ancienneté nette peut exclure certaines interruptions, comme un congé sans solde ou une rupture entre deux contrats. Excel peut gérer ces cas, mais la règle métier doit être définie avant la formule. Faut-il additionner plusieurs périodes, exclure des absences, proratiser un temps partiel ou conserver une ancienneté reprise ?

Pour des multi-contrats, la meilleure pratique consiste à calculer chaque période séparément, puis à additionner les jours ou les mois retenus selon la règle interne. Mieux vaut un tableau intermédiaire clair qu’une seule formule illisible, surtout si le fichier doit être audité ou partagé entre plusieurs personnes.

Automatiser sans perdre le contrôle

Dans un tableau de suivi, vous pouvez ajouter une mise en forme conditionnelle pour surligner les seuils de 2 ans, 5 ans, 10 ans ou 15 ans, si ces seuils correspondent à vos règles internes. Pour des volumes importants, un tableau croisé dynamique peut synthétiser les anciennetés par service, métier ou type de contrat.

Une macro VBA ou un simulateur interne peut aussi accélérer le travail, mais la priorité reste la même, des dates propres, une date de référence explicite et des formules contrôlées. Excel calcule vite, à condition de lui donner une règle claire.