Êtes-vous fatigué de calculer manuellement les heures de travail de vos employés chaque jour dans Google Sheets ? Ne vous inquiétez plus ! Avec ce tutoriel ultime sur les feuilles de calcul, vous pourrez facilement calculer les heures de travail par jour dans Google Sheets.
De jour comme de nuit, en semaine ou en week-end, vous pouvez calculer les heures de travail de vos employés en fonction de l’heure de début et de fin de travail. Plus besoin de passer des heures à faire des calculs fastidieux, il vous suffit d’entrer l’heure de début, l’heure de fin et le taux horaire normal ainsi que les heures supplémentaires. Les cellules correspondantes sont mises en évidence en orange dans la feuille de calcul. Le reste est pris en charge par la formule.
Calculer les heures de travail par jour dans Google Sheets n’est pas compliqué du tout. Vous pouvez suivre cette méthode pour gagner du temps précieux dans la gestion de la paie et du temps de travail de vos employés.
Google Sheets : Les heures de travail par jour, qu’est-ce que c’est ?
Dans cet exemple de données sur Google Sheets, la colonne A représente la date et l’heure de début du travail d’un employé. La colonne B montre la date et l’heure de fin du travail. Les deux sont au format date et heure.
Il est essentiel d’entrer l’heure de début et de fin du travail au format date et heure, surtout dans les emplois de jour et de nuit où l’heure de début et de fin peut-être sur deux jours différents. Par exemple, si un employé commence son travail le 26/04/18 et le termine le 27/04/18, les heures de travail seront réparties entre les deux jours.
Grâce à ce type d’entrée sur votre feuille de temps dans Google Sheets, vous pouvez calculer les heures de travail par jour.
Comment calculer les heures de travail par jour à partir de l’heure de début et de fin du travail
Si vous avez entré l’heure de début et de fin du travail de vos employés dans les colonnes A et B, vous pouvez utiliser la formule suivante dans la cellule D2. Vous n’avez rien d’autre à faire.
Je sais que cela peut sembler compliqué, mais cette méthode est très utile si vous suivez le format de données que j’ai mentionné précédemment.
La formule suivante vous permet de calculer les heures de travail par jour :
={"Date","Total des heures";query(ArrayFormula(query({int(A3:A),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col1");int(B3:B),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col2")},"Select Col1,Col2 where Col2 is not null")),"Select Col1, Sum (Col2) group by Col1 label Sum(Col2)''")}Cette formule retournera le total des heures de travail par jour comme indiqué dans la capture d’écran. Assurez-vous de recopier correctement les guillemets doubles pour éviter les erreurs d’analyse.
Nous avons maintenant les heures de travail normales par jour dans la colonne E. La prochaine étape consiste à calculer les heures supplémentaires et les heures de travail du week-end à partir de ces valeurs.
Avant cela, permettez-moi de vous expliquer cette formule plus en détail. Si vous êtes débutant sur Google Sheets ou si vous souhaitez simplement utiliser le modèle que je vais fournir, vous pouvez passer cette explication.
Vous pouvez accéder à mon modèle ici. Ouvrez ce fichier, puis faites-en une copie à partir du menu Fichier pour obtenir une version modifiable du fichier.
Explication de la formule : Calcul des heures de travail par jour dans Google Sheets
En réalité, la formule ci-dessus fait un récapitulatif par ligne. Elle recherche les mêmes jours dans différentes lignes et les totalise. Comment ?
Par exemple, je copie les mêmes données que précédemment dans un nouvel onglet de feuille et entre la formule suivante dans la cellule C3 :
=ArrayFormula(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),))Voici le résultat :
Cette formule calcule les heures de travail totales à partir de l’heure de début et de l’heure de fin. Elle ne se limite pas à cela. Si les dates de début et de fin sont différentes, la formule répartit simplement les heures de travail en conséquence.
Vous pouvez suivre le tutoriel mentionné précédemment pour comprendre le fonctionnement de cette formule. Mais une chose : dans ce tutoriel, la formule génère la durée totale plutôt que le nombre total d’heures.
Cela signifie que les colonnes C et D affichent une durée, tandis que dans notre cas, nous souhaitons obtenir le total des heures. Il y a seulement quelques modifications mineures à apporter à la dernière partie de la formule.
Vous pouvez placer cette formule #2 et la formule détaillée dans le tutoriel l’une à côté de l’autre et les comparer pour trouver les différences.
Revenons maintenant au tutoriel. La formule ci-dessus est la partie principale de la formule #1. Si vous vous référez à la capture d’écran ci-dessus, vous verrez que la formule ci-dessus #2 renvoie deux colonnes (colonnes C et D).
Grâce à une simple formule de requête dans Google Sheets, nous pouvons séparer les colonnes comme suit, et il y a une raison à cela :
=query(ArrayFormula(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),)),"Select Col1")=query(ArrayFormula(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),)),"Select Col2")=ArrayFormula(query({int(A3:A),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col1");int(B3:B),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col2")},"Select Col1,Col2 where Col2 is not null"))Comme vous pouvez le constater, il y a plusieurs occurrences des mêmes dates dans cette sortie. Dans la formule finale (formule #1), j’ai simplement additionné la colonne 2 en regroupant les dates de la colonne 1. C’est ainsi que vous pouvez calculer les heures de travail par jour dans Google Sheets.
Nous avons calculé les heures de travail par jour. Voyons maintenant comment calculer les heures normales et les heures supplémentaires à partir de là.
Dans la cellule G2, vous trouverez la formule suivante :
={"Total des heures normales";sum(ArrayFormula(IF(LEN(D3:D),if((weekday(D3:D)<>1)*(weekday(D3:D)<>7)>0,E3:E,),)))}Cette formule exclut les heures de travail du week-end et totalise les heures de travail normales. Le total, selon les données de l’exemple ci-dessus, est de 41 heures.
Et qu’en est-il des heures de week-end ? J’ai considéré les heures de travail du week-end comme des heures supplémentaires à part entière.
Pour calculer les heures supplémentaires du week-end, j’ai utilisé la formule suivante dans la cellule H2 :
={"Total des heures du week-end";sum(ArrayFormula(IF(LEN(D3:D),if((weekday(D3:D)=1)+(weekday(D3:D)=7)>0,E3:E,),)))}Et le total est de 14 heures.
Donc, selon les deux formules mentionnées ci-dessus, le total des heures de travail est de :
Heures normales + Heures de travail du week-end = 55 heures.
Maintenant, je déduis simplement les heures supplémentaires des heures normales de travail (en excluant les week-ends, car ils ont déjà été considérés comme des heures supplémentaires).
Dans la cellule I2, vous trouverez la formule suivante :
={"Total des heures supplémentaires";sum(ArrayFormula(if(IF(LEN(D3:D),if((weekday(D3:D)<>1)*(weekday(D3:D)<>7)>0,E3:E,),)>8,E3:E-8,)))}Si un employé a travaillé plus de 8 heures par jour, j’ai considéré les heures supplémentaires comme les heures supplémentaires. Vous pouvez modifier les deux occurrences du nombre 8 dans cette formule pour changer la base des heures supplémentaires.
Cela signifie que si un employé a travaillé 12 heures par jour, 8 heures sont considérées comme ses heures normales de travail et 4 heures sont considérées comme des heures supplémentaires. Mais j’ai exclu les week-ends. Cela signifie que dans le calcul ci-dessus, j’ai exclu le samedi et le dimanche.
Calcul final :
Dans la cellule H6 :
Heures normales totales – Heures supplémentaires totales (en excluant les week-ends) = Heures normales payées.
Dans la cellule H7 :
Heures du week-end + Heures supplémentaires totales = Heures totales payées pour les heures supplémentaires.
Voici comment vous pouvez calculer les heures de travail par jour, les heures supplémentaires, les heures de travail de nuit et les heures de travail du week-end à l’aide de Google Sheets.
