Calcul des heures de travail hebdomadaires et de la rémunération due dans Excel
Télécharger un modèle de calcul des heures de travail dans Excel 👇
Mise en place d’un tableau pour calculer les heures de travail
À titre d’exemple, nous proposons un calcul des heures travaillées sur une base hebdomadaire. Bien entendu, vous pouvez facilement appliquer cet exemple à une période de temps de durée différente.
Dans l’exemple ci-dessous, nous avons remplacé la date étendue (lundi 9 février 2015) par un format numérique personnalisé jj jj / mm pour des raisons d’espace. Dans ce format, les trois premières lettres “g” représentent le jour abrégé, suivies des valeurs “jj” pour le jour et “mm” pour le mois au format numérique. Le résultat de cette mise en forme personnalisée affiche la date au format suivant : Lun 09/02.
Vous devez inscrire les heures d’entrée et de sortie du travail dans les colonnes C, D, E et F.
Définition des Formules pour Calculer le Total des Heures Quotidiennes et Hebdomadaires
Dans cet exemple, nous supposons un temps de travail fractionné entre le matin et l’après-midi. Pour totaliser les heures de travail journalières, nous devons créer une formule qui additionne la différence entre les heures de sortie et d’entrée pour chaque période. Ainsi, pour l’heure du lundi saisie dans la cellule H3, vous devez entrer la formule suivante : “D3-C3 + F3-E2 : E3”.
Évidemment il est possible de faire glisser la Formule dans les cellules en dessous avec le bouton remplir pour obtenir les jours suivants. Une fonction de sommation = SOMME (H3 : H9) est entrée dans la cellule H11.
Attention! Le résultat renvoyé dans la cellule H11 est apparemment erroné. En réalité, le format numérique TIME lorsqu’il atteint la valeur 24 est remis à zéro. Par conséquent, la somme des premières 24 heures est absente du total de 17:20 heures. Pour formater correctement le résultat, dans la boîte de dialogue Format de cellule, cliquez sur l’onglet Nombre. Dans l’onglet Nombre, dans la colonne de gauche sélectionnez le format Heure et dans la colonne de droite choisissez le format 37:50:55, qui est utilisé spécifiquement pour compter les heures. L’application de cette mise en forme dans la cellule H11 affiche le résultat correct 41:20.
Calcul de la Différence entre les Heures, Si l’Heure de Sortie était un Jour Après Celle d’Entrée
S’il y a une heure de sortie le lendemain de l’heure d’entrée, vous pouvez procéder de deux manières distinctes. La première consiste à utiliser un format de date associé à l’heure, de cette façon Excel identifie la bonne différence entre les deux heures. La deuxième méthode, plus simple, consiste à appliquer une fonction SI, qui considère si le temps de sortie est inférieur au temps d’entrée. Plus précisément (voir figure ci-dessous), l’entrée est insérée dans la colonne A et la sortie dans la colonne B. la formule saisie dans la cellule C1 est la suivante : = SI (B2 <A2 ; B2 + 1 ; B2) -A2.
Cette Fonction Logique évalue si le temps saisi dans la cellule “sortie” est inférieur à la valeur saisie dans la cellule “entrée”. Si cette condition était vraie, la formule appliquée serait B2 + 1-A2. Évidemment, en faisant glisser la fonction SI dans les cellules ci-dessous, si la sortie était le même jour que l’entrée, la formule appliquée serait simplement B2-A2.
Transformation du Format de l’Heure en Nombre
Une fois le nombre total d’heures totalisé, il faut transformer le format horaire en un format numérique permettant de le multiplier par la valeur du salaire horaire. Excel enregistre le format de l’heure dans un numéro de série compris entre 0 et 1 pour une période de 24 heures. Ce n’est pas un hasard si 1 est la valeur numérique de série de l’unité heure-jour dans Excel.
D’un point de vue pratique, il vous suffit de multiplier le nombre total d’heures par 24. Cette opération transforme la subdivision sexagésimale des minutes et des secondes du format horaire en une valeur numérique centésimale. Par exemple, multiplier 41:20 par 24 donne 41,33, et vous pouvez ensuite multiplier ce nombre par le salaire horaire.
Calcul de la Rémunération et Application d’une Rémunération Extraordinaire
Pour illustrer, supposons que les 36 premières heures de travail par semaine soient rémunérées à 17,50 €, tandis que les heures suivantes le sont à 19,00 €. En calculant un total hebdomadaire de 41:20, les 36 premières heures seront multipliées par 17,50 et la différence (41:20 moins 36:00) sera multipliée par 19,00. Si le nombre total d’heures ne dépasse pas 36, le calcul du salaire se fera uniquement en multipliant le total des heures par le salaire ordinaire. Une formule SE est nécessaire pour vérifier si le total des heures hebdomadaires dépasse la limite de 36 heures. Dans les cas où cela se produit, des calculs sont effectués pour les heures supplémentaires, sinon le salaire ordinaire est appliqué.
.
La fonction SE s’exprimera ainsi : = SE (H12> 36 ; C12 * 36 + (H12-36) * C13 ; H12 * C12).
Application
Pour créer un modèle Excel basé sur votre description, je vais détailler les étapes à suivre et les formules à utiliser pour chaque section de votre feuille de calcul. Ce modèle permettra de calculer les heures de travail hebdomadaires et la rémunération due, y compris pour les heures supplémentaires.
Mise en Place du Tableau
- Configuration initiale : Créez un tableau avec les colonnes suivantes : Date (au format Lun 09/02), Heure d’entrée AM, Heure de sortie AM, Heure d’entrée PM, Heure de sortie PM, Total heures quotidiennes, Total hebdomadaire, Rémunération.
- Format de date : Pour la colonne Date, utilisez le format personnalisé
jjj jj/mm.
Formules pour les Calculs
- Total heures quotidiennes : Dans la colonne Total heures quotidiennes (mettons que c’est la colonne H), utilisez la formule suivante pour calculer la différence entre les heures d’entrée et de sortie pour chaque période et additionnez-les. Pour la cellule H3, par exemple, la formule sera
=(D3-C3) + (F3-E3). Assurez-vous que les heures sont au format [h]:mm pour gérer correctement les périodes supérieures à 24 heures. - Total hebdomadaire : Pour calculer le total hebdomadaire dans la cellule H11, utilisez
=SOMME(H3:H9). Assurez-vous que cette cellule est également formatée en [h]:mm pour éviter la réinitialisation après 24 heures. - Calcul de la différence en cas de jour suivant : Pour gérer les cas où l’heure de sortie est le lendemain de l’heure d’entrée, utilisez
=SI(B2<A2, B2+1, B2)-A2dans une colonne dédiée pour chaque période de travail. Remplacez A2 et B2 par vos cellules d’entrée et de sortie respectives. - Transformation du format de l’heure en nombre : Pour convertir les heures en format numérique afin de les multiplier par le salaire horaire, multipliez le total d’heures (par exemple, H11 pour le total hebdomadaire) par 24.
Calcul de la Rémunération
- Rémunération hebdomadaire : Pour calculer la rémunération, supposons que la cellule H12 contient le total des heures en format numérique (après multiplication par 24) et que les cellules C12 et C13 contiennent respectivement le tarif horaire normal et le tarif horaire pour les heures supplémentaires. Utilisez la formule suivante pour la rémunération dans une nouvelle cellule :
=SI(H12>36, 36*C12 + (H12-36)*C13, H12*C12).
Cette formule calcule la rémunération basée sur 36 heures au tarif normal (C12) et le reste au tarif des heures supplémentaires (C13). Assurez-vous de multiplier le total d’heures par 24 avant d’appliquer cette formule pour convertir le format horaire en nombre.
Notes Importantes
- Assurez-vous que toutes les cellules utilisées pour saisir les heures sont au format heure.
- Pour les formules impliquant des soustractions d’heures qui peuvent passer à un nouveau jour, assurez-vous de gérer correctement le passage au jour suivant pour éviter des valeurs négatives.
- Vérifiez et ajustez les formats de cellules pour les totaux et les rémunérations pour s’assurer qu’ils reflètent correctement les valeurs désirées.
Ce guide devrait vous permettre de configurer une feuille de calcul Excel répondant à vos besoins de gestion des heures de travail et de calcul de la rémunération.
Q1: Qu’est-ce qu’une feuille de calcul Excel?
R: Un programme informatique pour organiser, analyser et calculer des données.
Q2: Comment entrer les heures dans Excel?
R: Utilisez le format heure avec le séparateur “:”.
Q3: Comment calculer les heures de travail hebdomadaires?
R: Ajoutez les différences entre les heures d’entrée et de sortie.
Q4: Comment éviter les erreurs de calcul d’heure?
R: Utilisez le formatage de cellule approprié pour les heures.
Q5: Quelle formule utiliser pour calculer les heures travaillées?
R: Soustrayez l’heure de sortie de l’heure d’entrée.
Q6: Comment traiter les heures supplémentaires dans Excel?
R: Utilisez la fonction SI pour détecter les heures supplémentaires.
Q7: Comment convertir les heures en salaire?
R: Multipliez le nombre d’heures par le taux horaire.
Q8: Comment gérer les jours fériés dans le calcul des heures?
R: Soustrayez les heures des jours fériés du total hebdomadaire.
Q9: Comment obtenir un total précis des heures travaillées?
R: Utilisez la fonction SOMME pour additionner les heures.
Q10: Comment formater les résultats de calcul d’heure?
R: Utilisez un format de cellule approprié pour afficher l’heure.
Autres articles
