KHIZHNYAKOVCalculer l'ancienneté et la prime d'ancienneté sous Excel
Calcul de l'ancienneté et de la prime d'ancienneté sous Excel : périodes, jours calendaires — modèle Excel — Voir le modèle →
Calculer l'ancienneté d'un salarié semble facile tant qu'il n'a eu qu'un seul contrat. Dès qu'il y a plusieurs périodes, des interruptions ou des périodes comptées avec un coefficient particulier, on se retrouve à additionner des années, des mois et des jours à la calculatrice, avec des retenues approximatives. Et personne ne voit venir le jour où la prime change de palier.
Voici comment fiabiliser ce calcul dans Excel : tout passer en jours, additionner, puis reconvertir.
Le principe : tout compter en jours
Additionner « 2 ans 7 mois 12 jours » et « 1 an 9 mois 25 jours » à la main est source d'erreurs. La méthode sûre :
- Une ligne par période : employé, date de début, date de fin, type de période.
- Compter les jours de chaque période de façon inclusive (le jour de début et le jour de fin comptent) :
=fin-début+1. - Appliquer un coefficient si votre règlement en prévoit un (1 pour une période ordinaire, 1,5 ou 2 pour une période privilégiée, selon votre règlement).
- Additionner par employé.
- Reconvertir en années, mois et jours avec une convention simple et écrite : par exemple un mois = 30 jours, une année = 360 jours.
Exemple chiffré
Un salarié a deux périodes : du 1er septembre 2018 au 31 août 2021 (3 ans), et du 1er mars 2022 au 28 février 2025.
| Période | Jours calendaires | Coefficient | Jours pris en compte |
|---|---|---|---|
| 01/09/2018 – 31/08/2021 | 1 096 | 1 | 1 096 |
| 01/03/2022 – 28/02/2025 | 1 096 | 1 | 1 096 |
| Total | 2 192 |
Avec la convention 360 jours par an : 2 192 ÷ 360 = 6 ans, reste 32 jours = 1 mois et 2 jours. Ancienneté : 6 ans, 1 mois, 2 jours.
Les formules Excel
- Jours d'une période :
=D2-C2+1 - Jours pris en compte :
=E2*RECHERCHEV(F2;Types;2;FAUX), le tableau « Types » associant chaque type de période à son coefficient. - Total par employé :
=SOMME.SI(Employé;A2;Jours) - Années :
=ENT(total/360) - Mois :
=ENT(MOD(total;360)/30) - Jours :
=MOD(MOD(total;360);30) - Prime :
=RECHERCHEV(années;Barème;2;VRAI)avec le barème trié par ordre croissant des seuils (0 an → 0 %, 3 ans → 5 %, 5 ans → 10 %...).
Le VRAI final est important : il demande à Excel de retenir le dernier palier franchi, pas une correspondance exacte.
Le prochain seuil
Pour prévenir à l'avance : trouvez le premier seuil supérieur à l'ancienneté actuelle, convertissez l'écart en jours, ajoutez-le à la date du jour. Avec 6 ans et 32 jours d'ancienneté et un seuil à 7 ans : 360 − 32 = 328 jours restants environ. Un feu tricolore (« bientôt » à 90 jours, « urgent » à 30 jours, par exemple) permet de préparer les changements de paie.
Erreurs fréquentes
- Oublier le +1 : sans lui, chaque période perd un jour.
- Utiliser DATEDIF sur chaque période puis additionner : les restes de mois ne s'additionnent pas proprement. Mieux vaut sommer des jours.
- Barème non trié : avec
VRAI, un barème mal ordonné donne des primes fausses. - Périodes qui se chevauchent : ajoutez un contrôle de dates pour ne pas compter deux fois les mêmes jours.
- Convention changeante : fixez 30/360 une fois pour toutes et notez-le dans le fichier.
Les règles de prise en compte des périodes (quelles périodes comptent, avec quel coefficient) relèvent de votre règlement et de la réglementation locale : vérifiez-les avant d'appliquer un résultat.
Modèle Excel prêt à l'emploi
Le modèle « Calcul de l'ancienneté et de la prime d'ancienneté » applique cette méthode. Dans « Paramètres » : date de calcul, types de périodes avec coefficient (1 pour calendaire, 1,5 ou 2 pour privilégié selon votre règlement), barème de prime avec seuils et pourcentages, fenêtres « bientôt » et « urgent ». La feuille « Périodes » reçoit jusqu'à 2 000 périodes avec jours pris en compte et contrôle des dates.
La feuille « Employés » (jusqu'à 200 personnes) donne l'ancienneté totale en années, mois et jours, le pourcentage de prime, le seuil suivant, sa date d'atteinte, le temps restant et un feu tricolore. La « Synthèse » compte les personnes à chaque palier, liste celles dont le seuil est proche et propose deux graphiques. Le calcul n'utilise pas DATEDIF, ce qui garantit le même résultat dans Excel et LibreOffice.
Le fichier fonctionne sous Excel 2007 et versions ultérieures, sans macros, et il est payant. Les valeurs de l'exemple sont fictives et le calcul est indicatif.
FAQ
Faut-il compter le jour de début et le jour de fin ?
C'est la convention la plus courante pour une période d'ancienneté, mais elle dépend de votre règlement. Choisissez-en une et appliquez-la partout.
Que faire des périodes d'interruption ?
Ne les saisissez pas, ou saisissez-les avec un type dont le coefficient est 0, selon que votre règlement les compte ou non.
Comment vérifier un résultat ?
Recalculez une personne à la main avec la même convention (jours ÷ 360). Si le résultat diffère, cherchez d'abord un chevauchement de dates ou un +1 manquant.
Pourquoi 30/360 plutôt que les dates réelles ?
Cette convention donne un résultat stable, simple à expliquer et à reproduire. Si votre règlement précise une autre méthode, adaptez-la.
