Calcul ancienneté Excel : modèles et astuces

Comment calculer l’ancienneté d’un salarié sur Excel ? Existe-t-il une formule pour calculer l’ancienneté sur Microsoft Excel grâce à la date d’entrée du personnel ? Même s’il n’existe pas de formule spécifique dans notre tableur, il existe des fonctions simples qui vous permettront de la calculer. Vous pourrez alimenter vos tableaux de bord paie et RH, et ainsi, gagner du temps ! Avec quelques formules Excel bien utilisées dans notre feuille de calcul, vous verrez que la solution est à portée de main.

Dans ce tutoriel Excel, je vais vous expliquer comment calculer l’ancienneté d’une personne simplement.

Les calculs de dates qui seront abordés sont également disponibles en téléchargement. 🤩

Comment calculer l’ancienneté sur Excel à partir de la date d’entrée ?

Il existe plusieurs possibilités pour réaliser le calcul de l’ancienneté sur Excel. Les solutions les plus faciles seront les formules Excel DATEDIF et FRACTION.ANNEE.

Avec chaque fonction Excel, nous pourrons également nous servir des formules suivantes :

• La formule AUJOURDHUI ;
• La formule DATE ;
• La formule TRONQUE.

Voyons chacune des fonctions Excel et constatons les résultats dans vos feuilles de calcul.

Calcul ancienneté Excel : avec DATEDIF

Pour calculer l’ancienneté sur Excel avec DATEDIF à la date du jour, il faut utiliser la fonction suivante :

=DATEDIF( date_entrée ; AUJOURDHUI() ; “Y” )

Si la date d’entrée est contenue dans une des feuilles de votre classeur Excel, vous pourrez simplement la sélectionner.

Si nous voulons calculer l’ancienneté vis-à-vis d’une date spécifique, il faut supprimer la fonction AUJOURDHUI et la remplacer par la date voulue. Il suffira alors de sélectionner la cellule où est la date. Dans le cas où vous voudriez saisir dans votre cellule Excel la date directement, il sera nécessaire d’utiliser la formule DATE. Elle permet de convertir des nombres en format date. Le calcul Excel aura la forme suivante :

= DATEDIF( date_entrée ; DATE(2022;1;1) ; “Y”)

La fonction DATEDIF permet de calculer le nombre de périodes révolues entre deux dates. Cette fonction fait partie des fonctions avancées. Elle n’est pas disponible dans la barre d’outils (ruban Excel).

La formule Excel DATEDIF permet de compter les années, les mois ou les jours qui se sont entièrement écoulés entre deux dates clairement définies.

Pour calculer l’ancienneté sur Excel entre une date d’entrée du personnel et la date du jour, il sera nécessaire d’utiliser la fonction AUJOURDHUI. Elle n’a pas de paramètre. Il faut simplement ouvrir puis fermer les parenthèses une fois la formule Excel saisie.

La formule DATE permet d’indiquer à Microsoft Excel que nous voulons saisir une date dans une formule. Cette fonction Excel contient 3 paramètres. Dans votre cellule, il faut saisir l’année, le mois et le jour.

Dans l’exemple ci-dessous, j’ai une cellule Excel contenant la date d’entrée du salarié et une autre contenant la date du jour. Avec la fonction DATEDIF et en sélectionnant les plages de cellules, j’ai pu obtenir l’ancienneté du salarié directement dans Microsoft Excel.

Calcul ancienneté Excel : avec FRACTION.ANNEE

Pour calculer l’ancienneté d’un salarié sur Excel avec FRACTION.ANNEE, il faut se servir de la formule suivante :

=TRONQUE( FRACTION.ANNEE( date_entrée ;  AUJOURDHUI() ; 3 ) ; 0 )

Cette formule calcule l’ancienneté du salarié par rapport à la date du jour.

Si vous voulez calculer l’ancienneté à partir d’une date différente de la date du jour, vous pouvez cliquer sur la cellule contenant la date. Pour saisir directement dans la formule Excel la date, utilisez fonction Excel DATE.

La formule Excel FRACTION.ANNEE est une fonction avancée. Elle est disponible dans votre barre d’outils Excel (menu Formules). Elle mesure le nombre d’années s’étant déroulées entre des dates présentes dans plusieurs cellules. Comparativement à la formule Excel DATEDIF, elle ne calcule pas le nombre d’années écoulées depuis la date d’entrée du salarié. Elle calcule le nombre d’années révolues ainsi que le prorata de l’année entamée. 

La syntaxe de la fonction Excel FRACTION.ANNEE est la suivante : 

= FRACTION.ANNEE(date_début; date_fin; "base").

Le dernier paramètre est le paramètre « base ». Il permet de choisir la méthode de calcul. Le tableur Excel propose 4 calculs possibles :

• 0 – Etats-Unis – 30/360
• 1 – Réel/réel
• 2 – Réel/360
• 3 – Réel/365
• 4 – Europe – 30/360

Pour le calcul de l’ancienneté en années complètes, le calcul choisi n’a pas d’impact puisque nous souhaitons garder uniquement la partie à gauche de la virgule. Si vous venez à réutiliser cette formule Excel, je vous invite à vous pencher sur les modes « Réel/réel » ou « 30/360 ». Ce sont les modes que j’utilise le plus régulièrement dans mes tableaux de bord. Ils vont dépendre de l’usage et des accords en entreprise.

La formule Excel TRONQUE va réduire le résultat aux chiffres à gauche de la virgule. La synthase de la fonction est la suivante :

= TRONQUE ( calcul ; nb de chiffres après la virgule )

La fonction TRONQUE attend 2 paramètres. Le dernier est facultatif. Il permet uniquement de préciser le nombre de chiffres que nous voulons après la virgule. Nous n’en voulons pas. Nous pouvons saisir 0 ou rien puisqu’il s’agit du paramètre par défaut.

Si nous regardons à présent notre fichier Microsoft Excel :

Comment calculer l’ancienneté d’un salarié sur Excel en années et mois ?

Pour obtenir l’ancienneté d’un salarié sur Excel en année et mois, il faut saisir dans votre cellule Excel la formule suivante :

= DATEDIF(date_entrée ; AUJOURDHUI() ; “Y”) & “ ans et “ & DATEDIF(date_entrée ; AUJOURDHUI() ; “YM”) & “ mois”

Il vous est possible de recopier directement ce calcul dans votre barre de formule et d’adapter le contenu de la cellule en remplaçant la date d’entrée du salarié par une formule ou en cliquant sur la valeur. 

Le résultat sur votre fichier Excel sera de la forme : 12 ans et 5 mois.

Pour calculer l’ancienneté sur Excel par rapport à une date clairement définie, vous pouvez intervertir la formule Excel AUJOURDHUI avec la date souhaitée en utilisant la fonction DATE.

La fonction Excel DATEDIF permet d’obtenir le nombre de périodes écoulées entre deux dates définies. On peut obtenir le résultat en années, en mois ou en jours. Pour choisir entre les différentes périodes et utiliser des formules parfaites, il faut remplacer le dernier paramètre de la formule par :

• “Y” = années ;
• “M” = mois ;
• “D” = jours ;
• “YM” = mois, depuis la dernière date d’anniversaire ;
• “YD” = jours, depuis la dernière date d’anniversaire ;
• “MD” = jours, depuis le dernier “mois d’anniversaire”.

Il est très important de saisir les guillemets dans la barre de formule. Dans le cas contraire, Microsoft Excel risque de ne pas comprendre votre intention.

Dans notre exemple, la formule va mesurer deux valeurs :

• Années complètes = DATEDIF( DATE(2010;1;1) ; DATE(2022;6;6) ; « Y » ) = 12
• Mois complets depuis la dernière date d’anniversaire = DATEDIF( DATE(2010;1;1) ; DATE(2022;6;6) ; « YM » ) = 5

Dans votre formule de calcul, il faudra concaténer vos valeurs en vous servant de la fonction Excel CONCATENER ou en utilisant le caractère & (comme dans mon exemple).

= 12 & “ ans et “ & 5 & “ mois”

J’ai volontairement placé des espaces entre les valeurs pour rendre plus facile la lecture des formules dans ce tutoriel Excel. Il faudra que vous les retiriez pour que votre formule fonctionne correctement. Attention, les espaces se situant entre les guillemets sont très  importants. Si vous ne les saisissez pas dans votre barre de formule, vous aurez un résultat beaucoup moins esthétique : 12anset5mois

Comment calculer l’ancienneté d’un salarié sur Excel en années, mois et jours ?

Pour obtenir l’ancienneté d’un salarié en années, mois et jours, il faut saisir dans votre barre de calcul Excel la formule suivante :

= DATEDIF(date_entrée ; AUJOURDHUI() ; “Y”) & “ ans, “ & DATEDIF(date_entrée ; AUJOURDHUI() ; “YM”) & “ mois et ” & DATEDIF(date_entrée ; AUJOURDHUI() ; “MD”) & “ jours”

A nouveau, nous utilisons la fonction avancée DATEDIF. Cette formule nous permet d’obtenir ici le nombre d’années, le nombre de mois écoulés depuis la dernière date d’anniversaire et enfin le nombre de jours depuis le dernier mois complet.

Il est rare d’avoir besoin d’un tel niveau de détail pour le calcul de l’ancienneté dans Microsoft Excel. Par contre, la fonction Excel DATEDIF est à mettre dans tous vos tableaux de bord dès lors que vous voulez calculer des différences de dates en périodes révolues.

N’utilisez pas cette méthode ! 😱

Lorsqu’on parle du calcul de l’ancienneté dans Microsoft Excel, la plupart du temps, vos collègues vont vous conseiller un calcul simple. L’intégrer dans votre plage de données fera que votre reporting sera faux. Même si cette solution peut sembler juste au premier coup d’œil, elle ne l’est pas. Il faut absolument la supprimer.

Le calcul que la majorité des personnes vous conseillera de sasir dans votre barre de formule est la suivante : (date_du_jour - date_entrée) / 365. Si vous la mettez dans votre feuille de calcul Excel, vous pourrez être sûr(e) d’avoir des anomalies.. Je vous déconseille de les écouter et de copier la formule.

Cette formule n’est pas exacte. Toutes les années ne comptent pas 365 jours et plus le salarié aura de l’ancienneté, plus le résultat sera faux.

Dans l’exemple présent dans le fichier Excel disponible en téléchargement, j’ai plusieurs cellules. Dans les premières j’ai saisi une date d’entrée du salarié (01/01/20210). Le contenu des autres cellules sont les dates du jour. En utilisant la formule conseillée par la majorité des personnes, je découvre que la date d’anniversaire de l’entrée du salarié dans l’entreprise serait le 29 Décembre et non pas le 1er Janvier.

Conclusion

Vous savez maintenant comment calculer l’ancienneté d’un salarié sur Excel de différentes manières. 

Nous avons vu :

• comment mesurer l’ancienneté d’un salarié sur Excel avec la fonction DATEDIF
• calculer l’ancienneté d’une personne à partir de sa date d’entrée avec FRACTION.ANNEE
• réaliser un calcul de l’ancienneté en années et mois
• indiquer dans votre barre de formules différents calculs Excel afin d’obtenir l’ancienneté en années, mois et jours

La fonction Excel DATEDIF fait partie des fonctions avancées de Microsoft Excel, elle est cachée (absente du ruban). Par contre, elle est très intéressante et je vous conseille vivement de vous en saisir dans vos tableaux de bord et vos reportings.

J’espère que ce tutoriel Excel vous aura aidé à réaliser votre tableau Excel.

Si vous avez des questions ou des remarques, n’hésitez pas à laisser un commentaire ci-dessous.

À bientôt ! 👋🏻