Calculs sur les heures de nuit avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calculs sur les heures de nuit et heures supplémentaires

Le calcul des heures est spécifique sous Excel. Lorsqu'il est question de considérer des heures travaillées de nuit, la problématique se corse. Le défi devient encore plus grand quand les plages horaires chevauchent les heures de nuit, sans les recouper complètement. Ici, nous apportons une solution pour les dénombrer et les tarifer.



Dans l'exemple proposé par la capture, un tableau effectue le suivi hebdomadaire des heures travaillées par les salariés. Ces plages sont fractionnées pour intégrer les pauses. En fonction des heures de début et de fin, le calcul journalier des heures est effectué. Puis, sont livrées les heures supplémentaires et les heures de nuit. Grâce à ces résultats, l'administrateur peut en déduire le coût du travail hebdomadaire par personne.

Source et présentation
Pour réaliser ces travaux, nous proposons premièrement de récupérer ce tableau de suivi des heures de travail.

• Télécharger le classeur heures-nuit-majorees.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

En dessous de chaque salarié, vous notez la présence des lignes Total, Sup et Nuit. Ce sont elles qui doivent accueillir les calculs pour les heures respectives. Sur la droite du tableau hebdomadaire, des contraintes sont fixées. Il s'agit notamment du coût horaire normal et des pourcentages de majoration à appliquer pour les heures supplémentaires et les heures de nuit. Mais ce n'est pas tout, d'autres contraintes fixent les bornes définissant la plage durant laquelle les heures de nuit sont déclenchées. Enfin, une dernière référence indique en l'état que les heures supplémentaires sont calculées au-delà de la septième heure. Nos calculs devront faire référence à ces cellules. Ainsi, en modifiant certains de ces leviers, le modèle sera transposable pour toute entreprise.

De plus, une liste déroulante permet de choisir l'un des salariés en cellule O11. En fonction de ce choix, nous devrons extraire automatiquement les heures du salarié et calculer son coût.

Totaliser les heures de travail
Le premier calcul à opérer sur la ligne Total peut paraître trivial. Il s'agirait de soustraire les heures de début aux heures de fin. Mais la présence des heures de nuit complique le mécanisme. Dans ce contexte, il n'est pas rare qu'une heure de fin soit inférieure à une heure de début. Dès lors, la soustraction conduit à un résultat farfelu. Nous devons donc exploiter la fonction Mod comme nous l'avions démontré à l'occasion d'une formation sur les heures.

=Mod(Heure_fin- Heure_début; 1)

Cette méthode permet d'obtenir non pas le reste de la division, mais le reste des heures si l'on considère une horloge de 24 heures. Prenons pour exemple un salarié prenant son poste à 23 heures et sa pause à 3 heures du matin. Le raisonnement devient le suivant : 24 - 23 + 3 = 4. Nous obtenons bien la durée des heures de nuit effectuées par ce deuxième salarié. Il s'agit donc de la méthode universelle à appliquer pour totaliser les heures de travail en toutes circonstances.

• Sélectionner le premier total à calculer, soit la cellule C7,
• Saisir et valider la formule suivante :

=MOD(D5-C5;1) + MOD(D6-C6;1)

En tenant compte de la pause intercalée, nous calculons bien la différence entre les heures de fin et les heures de début respectives.

• Ensuite, cliquer et glisser la poignée du résultat à l'horizontale jusqu'au Vendredi,
• Copier la sélection encore active avec le raccourci clavier CTRL + C par exemple,
• Sélectionner le premier total pour le salarié suivant, soit la cellule C12,
• Coller la formule par le raccourci clavier CTRL + V par exemple,
• Puis, réitérer pour les totaux des deux autres salariés,

Nous obtenons bien les cumuls des heures travaillées pour chaque jour de la semaine et pour tous les salariés, y compris lorsque des heures de nuit sont réalisées.

Calculer les heures supplémentaires
Grâce à ce premier résultat, les heures supplémentaires se déduisent naturellement en tenant compte de la contrainte fixée en cellule P9. En l'état, les heures supplémentaires sont déclenchées au-delà de la septième heure. En revanche, nous devons transformer la donnée en valeur décimale. L'astuce que nous avions apprise à ce sujet, consiste à diviser les heures totalisées par une unité horaire ('1:00').

• En cellule fusionnée C8, construire et valider la formule suivante : =C7/'1:00'-$P$9,

Nous retranchons la valeur de la cellule P9 au total des heures converti en nombre décimal. Bien sûr, cette cellule P9 doit absolument être figée pour permettre la réplication sur les autres jours de la semaine et pour les autres salariés. Notez que le diviseur doit nécessairement être inscrit entre guillemets. Le résultat obtenu ne semble pas cohérent. La cause est le formatage de cellule calé sur les heures.

• Sélectionner de nouveau la cellule C8,
• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• En haut des propositions, choisir Standard,
• Ensuite, tirer la poignée du résultat jusqu'au Vendredi,
• Puis, comme précédemment, copier et coller la formule pour les autres salariés,

Naturellement, si vous modifiez la contrainte dans le tableau de bord en cellule P9, tous les cumuls d'heures supplémentaires s'ajustent dynamiquement.

Totaliser les heures de nuit
Réaliser le cumul des heures travaillées la nuit est une tâche qui s'annonce plus ardue. Un salarié peut très bien commencer en dehors du créneau et terminer à l'intérieur. Mais il peut aussi commencer à l'intérieur et finir en dehors. De même, il peut très bien travailler uniquement en heures de nuit. De plus, dans de nombreux cas, l'heure de fin est inférieure à l'heure de début sur l'exemple d'une personne prenant son poste à 22 heures et le quittant à 6 heures du matin. Bref, ces multiples conditions imposent d'exploiter la fonction conditionnelle Si et ses imbrications.

Cette fois, les cellules de la ligne Nuit ne sont pas fusionnées. Pour simplifier quelque peu le procédé, nous proposons de réaliser les calculs sur les deux tranches séparées. Ainsi, toutes les conditions à envisager ne seront pas à réunir.

• Sélectionner la cellule C9 pour le calcul de la première tranche de nuit,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Inscrire la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
• Désigner la première heure de début en cliquant sur sa cellule C5,
• Taper le symbole supérieur (>) pour l'inégalité à honorer,
• Désigner la première heure de fin en cliquant sur sa cellule D5,

Grâce à ce critère, nous cherchons donc à savoir si l'heure de début est supérieure à l'heure de fin. Il confirmerait la présence d'heures travaillées la nuit.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Désigner de nouveau la première heure de début en cliquant sur sa cellule C5,
• Taper le symbole supérieur suivi du symbole égal, soit : >=,
• Puis, désigner la première contrainte des heures de nuit en cliquant sur la cellule N9,
• Enfoncer la touche F4 du clavier pour figer cette dernière, ce qui donne : $N$9,

Ce calcul est en effet destiné à être reproduit sur les autres jours et sur les autres salariés. Ce deuxième critère, s'il est vérifié, indique que le salarié a pris son poste durant les heures de nuit. Et s'il recoupe le premier critère, nous savons que toutes les heures de cette première tranche ont été faites durant la nuit.

• Fermer la parenthèse de la fonction Et,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Réaliser alors le calcul des heures pour la première tranche, comme précédemment :

Mod(D5-C5;1)

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Inscrire de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

Nous le disions, de nombreux cas sont possibles. Nous devons donc imbriquer les fonctions conditionnelles pour les envisager tous.

• Inscrire la fonction pour vérifier l'un des critères émis, suivie d'une parenthèse, soit : Ou(,
• Construire le premier critère suivant : C5>D5,

Comme précédemment, nous cherchons à savoir si l'heure de début est supérieure à l'heure de fin. Mais cette fois, nous ne sommes pas dans un raisonnement exclusif grâce à la fonction Ou. Cette condition et celle à suivre peuvent être vérifiées indépendamment.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Puis, construire le critère suivant : D5>=$N$9,

Il ne faut pas oublier de figer la cellule N9. Nous voulons donc savoir si l'heure de fin se termine dans la tranche nuit et ce, indépendamment de l'heure de début. Il peut donc s'agir d'un salarié commençant à 18 heures sa première tranche et la terminant à 22 heures par exemple.

• Fermer la parenthèse de la fonction Ou,
• Taper un point-virgule (;) pour passer dans la branche Alors de cette deuxième fonction Si,
• Calculer le temps écoulé comme suit : Mod(D5-$N$9;1),

Dans le cas ou le salarié a pris son poste avant 21 heures, seules les heures suivantes doivent être considérées en horaire de nuit. Donc, nous retranchons la borne inférieure issue du tableau de bord à l'heure de fin pour cette première tranche.

• Taper un point-virgule (;) pour passer dans la branche Sinon de cette deuxième fonction Si,
• Inscrire une nouvelle fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Construire la nouvelle condition suivante : D5<=$O$9,

Dans la mesure où les contraintes précédentes n'ont pas été honorées, nous voulons vérifier que l'heure de fin de cette première tranche est bien inférieure à la borne de fin pour la plage horaire de nuit. Si tel est le cas, nous en concluons que toutes les heures travaillées sont effectivement de nuit.

• Taper un point-virgule (;) pour passer dans la branche Alors de cette troisième fonction Si,
• Construire alors le calcul suivant : Mod(D5-C5;1),

Sans restriction et comme nous le disions, nous comptabilisons toutes les heures.

• Taper un point-virgule (;) pour passer dans la branche Sinon de cette troisième fonction Si,
• Inscrire une dernière fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Poser le critère suivant : C5<=$O$9,

Nous cherchons donc à savoir si l'heure de début de cette dernière tranche n'est pas située au-delà de la borne des heures de nuit. Dans la mesure où les conditions précédentes ne sont pas satisfaites, cela signifie qu'il s'agit d'un salarié pouvant prendre son poste la nuit et le terminant après.

• Taper un point-virgule (;) pour passer dans la branche Alors de cette quatrième fonction Si,
• Puis, réaliser le calcul suivant : Mod($O$9-C5;1),

De cette manière, nous excluons toutes les heures au-delà de la période nuit.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Inscrire le chiffre 0 pour ne pas comptabiliser les heures de nuit dans tous les autres cas,
• Fermer les autres parenthèses des quatre fonctions Si imbriquées,
• Puis, valider la formule à l'aide de la touche Entrée du clavier,

Le premier résultat apparaît et les heures de nuit sont correctement calculées et extraites.

• Sélectionner de nouveau la cellule C9 et la copier (CTRL + C),
• La coller (CTRL + V) toutes les deux cases, soit en E9, G9, I9 et K9,

Le calcul de la seconde tranche est identique. Mais il doit faire intervenir ses propres heures de début et de fin. En d'autres termes, les cellules C5 et D5 doivent être remplacées par C6 et D6.

• En cellule D9, adapter la formule précédente comme suit :

=SI(ET(C6>D6; C6>=$N$9); MOD(D6-C6;1); SI(OU(C6>D6; D6>=$N$9); MOD(D6-$N$9; 1); SI(D6<=$O$9; MOD(D6-C6; 1); SI(C6<=$O$9; MOD($O$9-C6; 1); 0))))

• Copier la cellule de ce calcul toutes les deux cases jusqu'au Vendredi,
• Sélectionner alors toutes les heures de nuit, soit la plage de cellules C9:L9,
• La copier et la coller pour les autres salariés,

Là encore, en modifiant quelques contraintes du tableau de bord, vous constatez que tous les calculs s'actualisent parfaitement.

Totaliser les heures du salarié
Désormais, nous devons réaliser la consolidation des heures travaillées pour un salarié désigné à l'aide de la liste déroulante en cellule O11. Ces éléments, nous devons les extraire indépendamment pour dissocier le cumul total des heures d'une part et les heures supplémentaires ainsi que les heures de nuit d'autre part. Il suffit donc de réaliser la somme des résultats que nous venons de calculer. Mais pour cela, nous devons être en mesure de pointer sur la ligne du tableau correspondant au salarié choisi. Cette somme peut être déplacée grâce à l'utilisation de la fonction Excel Decaler. Mais pour que celle-ci opère le bon décalage en ligne, elle doit faire appel à la fonction Equiv. Cette dernière renseignera sur la ligne à sommer en cherchant la position du salarié dans le tableau.

• Sélectionner la cellule pour le résultat des heures totales, soit O12,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la première des heures du tableau en cliquant sur la cellule C5,

Nous définissons ainsi le point de départ de la plage pour la somme des heures. Celui-ci est situé en regard du nom du premier salarié. Il est bien sûr amené à se déplacer, premièrement pour pointer sur la ligne du total et deuxièmement pour pointer précisément sur celle du salarié choisi.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,

C'est ainsi que doit intervenir la fonction Equiv. En trouvant la position du salarié désigné, elle fixera le déplacement vertical à opérer.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le salarié choisi comme élément de recherche en cliquant sur sa cellule O11,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la plage contenant notamment les noms des salariés, soit : B5:B24,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Puis, fermer la parenthèse de la fonction Equiv,
• Ajouter une unité à ce résultat, soit : +1,

La ligne du total des heures est systématiquement placée deux rangées en-dessous du nom du salarié. La fonction Equiv produit naturellement le premier décalage d'une unité par rapport à la position de départ incluse dans le décompte. Avec cette seconde unité, nous nous trouvons placés sur la ligne des heures à totaliser pour le salarié spécifié.

• Taper deux points-virgules, soit : ;;, pour ignorer le décalage en colonne,

En effet, nous sommes déjà placés sur la première des heures à intégrer dans le calcul pour le salarié choisi. Nous nous retrouvons donc positionnés dans l'argument de la hauteur pour la fonction Decaler. La somme doit être réalisée sur une seule et même ligne.

• Taper le chiffre 1 pour réaliser la somme sur cette même ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la largeur,
• Saisir le nombre 10 pour intégrer toutes les cases du Lundi au Vendredi,
• Fermer la parenthèse de la fonction Decaler,
• Fermer la parenthèse de la fonction Somme,
• Puis réaliser la division : /'1:00', pour convertir le total en nombre décimal,
• Valider le calcul à l'aide de la touche Entrée du clavier,
• Avec la liste déroulante de la section Nombre du ruban Accueil, choisir le format standard,

Nous obtenons bien la somme des heures effectuées pour le salarié. Et si vous changez son nom avec la liste déroulante en O11, le total s'actualise en cohérence avec le salarié. La syntaxe de la formule que nous avons bâtie est la suivante :

=SOMME(DECALER(C5; EQUIV(O11; B5:B24; 0)+1; ; 1;10))/'1:00'

Pour le cumul des heures supplémentaires, le calcul est le même à deux détails près. Tout d'abord, la conversion en nombre décimal ne doit pas être opérée. Les heures supplémentaires ont déjà subi cette transformation dans le tableau. Ensuite, le décalage en ligne doit être incrémenté d'une unité de plus pour pointer sur la ligne des heures supplémentaires du tableau. En O13, la syntaxe à adapter est donc la suivante :

=SOMME(DECALER(C5; EQUIV(O11; B5:B24; 0)+2; ; 1; 10))

Plutôt que d'adapter ce calcul, nous aurions pu construire une formule unique à répliquer par la poignée. Pour cela, il aurait fallu figer correctement les cellules impliquées. Et pour que le décalage en ligne progresse avec le calcul sur les lignes du dessous, nous aurions dû employer la fonction Ligne sur la cellule A1 par exemple. Il aurait ainsi premièrement retourné une incrémentation d'une unité, puis de deux puis de trois. Mais, comme la conversion n'était pas de mise ici, nous aurions de toutes façon dû intervenir sur le deuxième de ces calculs.

Enfin, pour totaliser les heures de nuit du salarié, il suffit donc d'incrémenter le décalage de deux unités supplémentaires par rapport au premier calcul :

=SOMME(DECALER(C5; EQUIV(O11; B5:B24; 0)+3; ; 1; 10))/'1:00'

Ensuite, il ne faut pas oublier d'appliquer le format standard à la cellule du résultat.



Enfin, pour calculer les coûts associés à ces heures, il faut exploiter les contraintes du tableau de bord. Pour les heures normales : =(O12-O13-O14)*N5. Avant d'appliquer le coût horaire de base, on retranche les heures supplémentaires et les heures de nuit au cumul calculé. Pour les heures supplémentaires : =O13*N5*(1+O5). On applique la majoration de 15% située en cellule O5. Pour les heures de nuit : =O14*N5*(1+P5). On applique la majoration de 25% inscrite en cellule P5. Dès lors, il ne reste plus qu'à faire la somme de ces trois résultats pour obtenir le coût hebdomadaire des heures travaillées par salarié.