Si vous arrivez sur cet article, c’est que nous faisons partie de la même team ! 👋
On ne cherche pas un calcul ETP Excel par hasard. Vous faites partie d’un service RH/paie/compta et vous devez réaliser un reporting pour votre Direction. On vous demande de calculer le nombre d’équivalents temps plein.
C’est une des tâches les plus courantes. Je suis toujours étonné que Microsoft Excel ne l’ai pas encore intégrée dans ses fonctions de base.
Heureusement pour vous, je vais vous expliquer dans cet article comme faire un calcul ETP sur Excel. Il y a plusieurs méthodes de calcul et quelques subtilités. Pour vous simplifier la tâche, j’ai concocté un super fichier. Il est disponible en téléchargement. C’est gratuit et vous pourrez y retrouver les formules qui vont vous être expliquées. Attention, je vous invite à consulter l’article pour bien comprendre les subtilités de construction du fichier. Vous êtes prêt(e)? C’est parti !
Télécharger le modèle de calcul ETP Excel.
Je vous propose des modèles d’états sous Excel. Vous y trouverez bien évidemment le tableau excel calcul effectif entreprise. Outre le calcul ETP, vous pourrez y trouver d’autres états à destination des services RH/Paie/Compta. Ces modèles vous permettront de produire le reporting demandé par votre Direction. Ils sont faciles à utiliser et à personnaliser. Vous pourrez vous en servir comme tableau de bord mensuel. Dans la suite de l’article je vous explique comment utiliser le modèle pour le calcul des ETP.
Téléchargez gratuitement les modèles Excel de Cleex. Vous y trouverez l’ensemble des modèles et calculs évoqués dans nos articles.
C’est gratuit 🤩
Qu’est ce que c’est un ETP (équivalent temps plein) ?
Un équivalent temps plein (ETP) est une unité de mesure. Elle est régulièrement demandée par différentes administrations pour des raisons d’enquête ou de recensement.
Elle correspond au nombre d’heures effectivement travaillées par un salarié au cours d’une période donnée, divisé par le nombre d’heures normalement travaillées au cours de cette même période. Cette unité permet de comparer le travail effectué par deux salariés, même si leur temps de travail n’est pas le même.
Par exemple : Un salarié travaille 40 heures par semaine et un autre salarié travaille 32 heures par semaine. Le premier travaille 1 ETP et le second 0,8 ETP. Pourquoi ? Parce que les deux ont travaillé 320 heures en 4 semaines (40 x 8). Nous divisons donc ces 320 heures travaillées par le nombre d’heures qui auraient été travaillées s’ils avaient tous deux travaillé à temps plein : 320/400 = 0.8.
Pourquoi calculer le nombre d’ETP ?
L’ETP est une excellente mesure pour évaluer les effectifs d’une entreprise. Elle est plus précise que le nombre de salariés car elle prend en compte le volume horaire travaillé par chaque salarié. Dans le calcul ETP annuel, on prend également en compte les périodes où le salarié a été présent. C’est donc un indicateur à intégrer dans vos reportings RH et paie.
La mesure de l’ETP rentrera aussi dans le calcul de la masse salariale. A chaque salarié, il va falloir affecter une rémunération (brute, chargée) auquel il faudra mettre un ratio de présence sur le mois en cas d’entrée ou de sortie de la personne de l’entreprise. Ce ratio sera l’ETP mensuel sur le salarié.
Comment calculer l’ETP mensuel sur Excel ?
Pour calculer l’ETP mensuel sur Excel, il y a 2 méthodes :
- En utilisant les fonctions de base pour Excel. Pour cela il faudra la date d’entrée et de sortie de chaque salarié ainsi que le taux travaillé (temps partiel).
- En utilisant une fonction personnalisée. Il faudra les mêmes données mais la lisibilité de la formule sera beaucoup plus facile.
Je vais vous expliquer chacune des méthodes. Ma préférence va à la seconde par sa lisibilité et la réduction du risque d’erreur de saisie.
Pour rendre plus simple la compréhension du calcul ETP Excel, nous allons décomposer les différents cas dans lequel nous pouvons nous retrouver :
- Le salarié est présent sur toute la période (tout le mois ou toute l’année) ;
- Le salarié a intégré l’entreprise pendant la période (sur le mois ou l’année) ;
- Le salarié a quitté l’entreprise pendant la période (sur le mois ou l’année) ;
- Le salarié a intégré et a quitté l’entreprise pendant la période (sur le mois ou l’année).
Il faut que vous ayez les informations suivantes : date d’entrée, date de sortie, taux travaillé. Vous devriez avoir un tableau qui ressemble à l’exemple ci-dessous (tableau disponible en téléchargement).
Certaines étapes du processus de création du tableau de calcul ETP Excel pourront vous sembler difficiles. Ne vous en faites pas, je vais vous expliquer le plus simplement possible comment adapater ce qui va vous être présenté. D’ici quelques minutes, vous aurez créé votre tableau de suivi des équivalents temps plein.
En utilisant les fonctions de base d’Excel
La première solution pour effectuer un calcul ETP Excel est d’utiliser les fonctions déjà présentes dans Excel. C’est la solution généralement choisie sauf quand on fait le travail à la main ! 😕 Si vous avez été dans ce cas, ce temps est révolu. Nous allons obtenir le tableau suivant avec une seule formule (une trèèèèèèès longue formule).
Pour cela, j’ai créé un tableau calcul ETP Excel comme ci-dessus. Les entêtes des colonnes sont les dates des premiers jours de chaque mois. J’ai voulu faire la formule la plus souple de façon à ce que vous puissiez l’étendre à l’ensemble de votre tableau sans difficulté. Pour cela nous allons utiliser les fonctions : SI(), DATE(), ANNEE(), MOIS(), ET(), OU().
Nous avons identifié 3 situations possibles. Voyons chacune d’entre elles avant d’obtenir la formule complète.
Le salarié est présent sur toute la période
Le premier cas est le plus facile. Si le salarié est présent sur toute la période alors le résultat est 1. Pour vérifier s’il est présent sur toute la période, il faut que sa date d’entrée soit inférieure à la date de début de la période et qu’il n’ait pas de date de sortie ou que celle-ci soit supérieure au dernier jour de la période.
=SI(
___ET(
______date_entree<=debut_periode
______;OU(date_sortie>=fin_periode;date_sortie="")
___)
;1
;0)
Le salarié a quitté l’entreprise pendant la période (sur le mois ou l’année)
Si le salarié a quitté l’entreprise pendant la période, il faut calculer son temps de présence en faisant nombre de jours de présence / nombre de jours sur la période. On obtient le nombre de jours de présence en faisant date où il a quitté l'entreprise - date de début de la période + 1. On ajoute le +1 pour compter le dernier jour du salarié.
Les conditions à remplir sont que la date d’entrée soit inférieure au début de la période et il faut que la date de sortie soit dans la période.
SI(
___ET(
______date_entree<=debut_periode
______;date_sortie<fin_periode
______;date_sortie>=debut_periode
___)
;(1+date_sortie-debut_periode)/(fin_periode-debut_periode)
;0)Le salarié a intégré l’entreprise pendant la période
Si le salarié est rentré dans l’entreprise pendant la période, il faut calculer son temps de présence en faisant nombre de jours de présence / nombre de jours sur la période. On obtient le nombre de jours de présence en faisant dernier jour de la période - date d'entrée.
Les conditions à remplir sont que la date d’entrée soit supérieure au début de la période et inférieure à la date du dernier jour de la période. Il faut que la date de sortie soit absente ou supérieure au dernier jour de la période.
SI(
___ET(
______date_entree>debut_periode
______;date_entree<fin_periode
______;OU(date_sortie>=fin_periode;date_sortie="")
___)
;(fin_periode-date_entree)/(fin_periode-debut_periode)
;0)
Le salarié a intégré et a quitté l’entreprise pendant la période (sur le mois ou l’année)
Dans ce cas, les conditions sont que les dates d’entrée et de sortie soient comprises entre le 1er et le dernier jour de la période. Le calcul sera date de (sortie - date d'entrée + 1) / nombre de jours sur la période.
SI(
___ET(
______date_entree>debut_periode
______;date_entree<fin_periode
______;date_sortie>debut_periode
______;date_sortie<fin_periode
___)
;(1+date_sortie-date_entree)/(fin_periode-debut_periode)
;0)Maintenant il ne vous reste plus qu’à remplacer les dates d’entrée, de sortie, de début et de fin de période par vos cellules sans oublier de mettre les dollars pour effectuer votre calcul ETP Excel.
Dans le tableau disponible en téléchargement, j’ai calculé le début et la fin de chaque période en fonction des entêtes des colonnes. La cellule d’entête contient le 1er jour du mois. Pour calculer le dernier jour du mois j’ai fait :
SI( MOIS( debut_periode ) = 12 ; DATE( ANNEE( debut_periode ) + 1 ; 1; 1 ) ; DATE( ANNEE( debut_periode ) ; MOIS( debut_periode ) + 1 ; 1 ) )
Ainsi, je calcule le premier jour du mois suivant en reconstituant la date. La formule complète est disponible ci-dessous.
=SI(
___ET(date_entree="";date_sortie="")
___;0
___;(
______SI(
_________ET(
____________date_entree<=debut_periode
____________;OU(date_sortie>=fin_periode;date_sortie="")
_________)
_________;1
_________;0)
_________
______+SI(
_________ET(
____________date_entree>debut_periode
____________;date_entree<fin_periode
____________;OU(date_sortie>=fin_periode;date_sortie="")
_________)
_________;(fin_periode-date_entree)/(fin_periode-debut_periode)
_________;0)
_________
______+SI(
_________ET(
____________date_entree<=debut_periode
____________;date_sortie<fin_periode
____________;date_sortie>=debut_periode
_________)
_________;(1+date_sortie-debut_periode)/(fin_periode-debut_periode)
_________;0)
______+SI(
_________ET(
____________date_entree>debut_periode
____________;date_entree<fin_periode
____________date_sortie>debut_periode
____________;date_sortie<fin_periode
_________)
_________;(1+date_sortie-date_entree)/(fin_periode-debut_periode)
_________;0)
___)
)*taux_travaillé
ETP prorata vs ETP mois complet
C’est une notion que vous ne verrez pas ailleurs. Pour tout vous dire, je n’ai même pas réussi à trouver le véritable nom à cette notion. Suivant l’entreprise dans laquelle vous êtes, il peut y avoir des disparités sur le calcul des ETP.
Prenons un exemple simple : vous avez un salarié qui entre dans l’entreprise le 1er février et la quitte le 28 février. Si on calcule l’ETP annuel du salarié, on devrait avoir 28 jours / 365 jours = 0,77 ETP annuel. C’est ce que j’appelle le calcul de l’ETP au prorata.
Dans d’autres entreprises, on vous dira que vu qu’il était présent tout le mois de février, son ETP annuel doit être calculé de la façon suivante 1 mois / 12 mois = 0,83 ETP annuel. C’est ce que j’appelle l’ETP mois complet.
Dans la section précédente nous avons calculé pour chaque mois des ETP mensuels au prorata. Si nous faisons le total de chaque ligne nous obtenons l’ETP annuel type mois complet.
Dans la prochaine section je vous explique comment créer des fonctions personnalisées en intégrant ces 2 types de cas.
Avec une fonction personnalisée (vba)
Vous allez peut être avoir du mal à me croire mais la solution de la fonction personnalisée est plus simple pour plusieurs raisons :
- Le code est déjà écrit ;
- L’intégration dans votre fichier est simple ;
- Le risque d’erreur est moindre.
Même si vous n’êtes pas à l’aise avec cette solution, je vous invite à la tester. Je vais vous expliquer comment. Je ne vais pas vous expliquer comment le code a été conçu. Il est assez explicite pour l’ETP au prorata. Pour l’autre, ça vous demanderait un peu de bagage technique. Ce n’est clairement pas le but de cet article.
Passons à l’intégration de ces fonctions dans votre tableau de calcul ETP Excel.
Récupération du(des) code(s)
Les codes sont présents ci-dessous. Prenez celui que vous voulez ou les deux si vous voulez tester leurs différences. Copiez le code et passez à la section suivante.
Function ETP(debut_periode, fin_periode, date_entree, date_sortie)
ETP = 0
'Vérification des formats
If Not (IsNumeric(debut_periode) Or IsDate(debut_periode)) Then
ETP = "#date_attendue_arg1"
Exit Function
End If
If Not (IsNumeric(fin_periode) Or IsDate(fin_periode)) Then
ETP = "#date_attendue_arg2"
Exit Function
End If
If Not (IsNumeric(date_entree) Or IsDate(date_entree)) Then
ETP = "#date_attendue_arg3"
Exit Function
End If
If Not (IsNumeric(date_sortie) Or IsDate(date_sortie) Or date_sortie = "") Then
ETP = "#date_attendue_arg4"
Exit Function
End If
'Si présence complète
If date_entree <= debut_periode And (date_sortie >= fin_periode Or date_sortie = "") Then
ETP = 1
Exit Function
End If
' Si date d'entrée pendant la période et date de sortie en dehors
If (date_entree >= debut_periode And date_entree <= fin_periode) And (date_sortie > fin_periode Or date_sortie = "") Then
ETP = (fin_periode - date_entree + 1) / (fin_periode + 1 - debut_periode)
Exit Function
End If
' Si date d'entrée pendant la période et date de sortie dedans
If (date_entree >= debut_periode And date_entree <= fin_periode) And (date_sortie < fin_periode) Then
ETP = (date_sortie + 1 - date_entree) / (fin_periode + 1 - debut_periode)
Exit Function
End If
' Si date d'entrée en dehors de la période et date de sortie dedans
If date_entree < debut_periode And (date_sortie < fin_periode And date_sortie >= debut_periode) Then
ETP = (date_sortie + 1 - debut_periode) / (fin_periode + 1 - debut_periode)
Exit Function
End If
End FunctionFunction ETPMOISCOMPLET(debut_periode, fin_periode, date_entree, date_sortie)
etp_temp = 0
'Vérification des formats
If Not (IsNumeric(debut_periode) Or IsDate(debut_periode)) Then
ETPBIS = "#date_attendue_arg1"
Exit Function
End If
If Not (IsNumeric(fin_periode) Or IsDate(fin_periode)) Then
ETPBIS = "#date_attendue_arg2"
Exit Function
End If
If Not (IsNumeric(date_entree) Or IsDate(date_entree)) Then
ETPBIS = "#date_attendue_arg3"
Exit Function
End If
If Not (IsNumeric(date_sortie) Or IsDate(date_sortie) Or date_sortie = "") Then
ETPBIS = "#date_attendue_arg4"
Exit Function
End If
' Boucle sur les années
For annee = Year(debut_periode) To Year(fin_periode)
'Calcul des mois sur l'année civile
If annee <> Year(fin_periode) Then
mois_max = 12
Else
mois_max = Month(fin_periode)
End If
If annee <> Year(debut_periode) Then
mois_min = 1
Else
mois_min = Month(debut_periode)
End If
' Boucle sur les mois de la période
For mois = mois_min To mois_max
debut_mois = DateSerial(annee, mois, 1)
fin_mois = DateSerial(annee, mois + 1, 0)
etp_mois = ETP(debut_mois, fin_mois, date_entree, date_sortie)
If etp_mois > 0 Then
etp_temp = etp_mois + etp_temp
End If
Next mois
Next annee
ETPMOISCOMPLET = etp_temp
End FunctionCréation de la macro dans votre fichier calcul ETP Excel
A présent ouvrez votre fichier et tapez Alt+F11 ou Alt+Fn+F11. Vous allez attérir dans le VBE (visual basic editor). Faites un clic droit sur Modules puis Insertion et enfin Module. Coller le(s) code(s) précédemment copié(s) dans la fenêtre qui vient d’être générée.
Vous venez de vous doter d’une fonction personnalisée. Si c’est votre 1ère, félicitations ! 🎉
Un point de vigilance, il faut enregistrer votre document au format xlsxm (format prenant en charge les macros). Dans le cas contraire, vos fonctions seront effacées à votre prochain enregistrement. L’art de mettre à la poubelle un boulot si joliment réalisé.
Utilisation des fonctions
Contrairement aux fonctions natives d’Excel, vous n’aurez pas d’aide lorsque vous taperez votre formule dans votre cellule. Les fonctions personnalisées ont été pensées afin de rendre le calcul d’ETP le plus facile. Les fonctions contiennent 4 arguments qu’il faudra saisir en les séparant par des points-virgules.
- Date de début de période
- Date de fin de période
- Date d’entrée du salarié
- Date de sortie du salarié
Pour tous les arguments, des éléments au format date est attendu sauf pour la date de sortie du salarié où une valeur vide est acceptée.
Pour le calcul d’un ETP au prorata, on a une formule de la forme :
=ETP( debut_periode ; fin_periode ; date_entree ; date_sortie )
Pour le calcul d’un ETP mois complet, on a une formule de la forme :
=ETPMOISCOMPLET( debut_periode ; fin_periode ; date_entree ; date_sortie )
L’ETP au prorata va calculer le prorata du temps de présence peu importe la période. Le maximum étant alors 1.
L’ETP mois complet va calculer le nombre d’ETP au protata mensuel. Il faudra par la suite le diviser par le nombre de mois sur la période pour ramener la valeur à 1.
Si je ne vous ai pas perdu, il manque un élément : le taux travaillé. Je ne l’ai pas intégré dans la formule. Il suffit juste de multiplier le résultat obtenu par le taux travaillé pour avoir la valeur exacte du nombre d’ETP.
Et maintenant ?
Si vous avez suivi jusqu’ici cet article, vous devez à présent avoir en votre possession un tableau de calcul ETP Excel.
Ce tableau et surtout les formules qui sont intégrées pourront vous servir pour générer vos reportings, pour initier un travail de budgétisation de la masse salariale ou pour répondre à diverses enquêtes légales.
Je vous mets en dessous des articles liés pouvant vous intéresser. Inscrivez-vous à la newsletter si vous ne voulez rien râter des articles à venir. Bye !
