Comment calculer les Heures de Nuit dans Excel?
Dans ce tutoriel, je vais vous montrer comment calculer précisément les heures de nuit travaillées avec Excel, avec des explications claires et un exemple concret. Vous n’avez pas besoin d’être un expert, suivez simplement les instructions et téléchargez le fichier d’exemple pour pratiquer ! Si vous êtes livreur, infirmier ou gestionnaire en Ressources Humaines, ce tutoriel va vous simplifier la vie !
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons reprendre le tableau utilisé dans la vidéo précédente avec les horaires réalisés par un livreur durant une semaine de travail.
Dans les cellules A9 jusqu'à A13 nous avons les dates de chacune des journées, et dans les colonnes B et C les heures de début et de fin de travail.
Cela nous avait permis de déterminer la durée de travail total dans la colonne D même si la plage horaire est à cheval sur deux jours, et nous allons maintenant souhaiter obtenir la durée de travail de nuit dans la colonne E.
Nous retrouvons également dans les cellules H9 et H10 les horaires utilisés par l’entreprise pour déterminer les heures de nuit.
Vous l’aurez bien compris, l’objectif de ce tutoriel sera ici de déterminer combien d’heures, dans la plage de travail, tombent dans cette période nocturne.
2. Le calcul des heures de nuit
Le calcul des heures de nuit est un peu complexe, car il doit tenir compte des spécificités des plages horaires, notamment lorsque celles-ci chevauchent minuit. C'est pourquoi il est indispensable de décomposer la formule en deux parties pour obtenir des résultats fiables dans toutes les situations.
Pour simplifier le processus, nous allons décomposer la formule en deux parties en fonction de l’heure de prise de poste et des heures de nuit :
Une première partie va permettre de calculer les heures réalisées avant la fin de la nuit
La seconde partie va prendre en compte que la plage de travail chevauche minuit.
2.1. L’heure de début est avant la fin de la nuit
Ici, l’heure de début de travail (B9) est antérieure à l’heure de fin des heures de nuit (H10).
Cela signifie que la plage horaire chevauche directement la fin des heures de nuit.
Nous allons alors utiliser la formule :
MIN(C9;$H$10)-B9
Cette fonction prend la plus petite valeur entre :
C9 : L’heure de fin de travail.
H10 : L’heure de fin des heures de nuit.
Cela nous permet de limiter le calcul à la période nocturne définie.
Si la fin du travail dépasse H10, seule la partie jusqu’à H10 sera comptabilisée.
Ensuite, nous retirons l’heure de début de travail (B9) pour calculer la durée réelle passée dans les heures de nuit.
Dans certains cas, cette formule retournera une erreur, car le résultat retourné est négatif, ce qui n’est pas permis dans la gestion des heures d’Excel.
Pour corriger le problème, nous comparons la formule avec 0 afin de ne récupérer que la plus grande des valeurs, ce qui va permettre de
2.2. L’heure de début est après l’heure de fin de nuit
Ensuite, nous allons prendre en compte le cas où l’heure de début de travail (B9) est postérieure à l’heure de fin des heures de nuit (H10).
Cela signifie que la plage horaire chevauche minuit, et l’heure de fin des heures de nuit se situe sur le jour suivant.
MIN($H$10+1;C9+SI(C9<B9;1;0))-MAX($H$9;B9)
Cette formule est un peu plus complexe, car nous considérons que les heures de fin sont situées sur la journée suivante, nous ajoutons donc « 1 » pour obtenir les heures correspondantes au lendemain :
$H$10+1
Ajoute 1 à l’heure de fin des heures de nuit (H10) pour indiquer qu’elle se situe sur le jour suivant (puisque minuit correspond à 1 en format Excel).
Puis, nous ajustons également l’heure de fin de travail :
C9+SI(C9<B9;1;0)
Si l’heure de fin (C9) est antérieure à l’heure de début (B9), cela signifie que la plage de travail traverse minuit. On ajoute donc +1 pour corriger cet écart.
Ensuite, nous comparons ces deux horaires, afin de récupérer la plus petite de ces valeurs :
MIN($H$10+1;C9+SI(C9<B9;1;0)) :
Nous pouvons ensuite soustraire à ce résultat l’heure de début qui correspond à la plus grande des valeurs entre :
- L’heure de début des heures de nuit (H9).
- L’heure de début de travail (B9).
MAX($H$9;B9) :
Comme sur la première formule, nous évitons d’obtenir une erreur avec la fonction MAX :
MAX(MIN($H$10+1;K13+SI(K13<J13;1;0))-MAX($H$9;J13);0)
Pour obtenir le total des heures de nuit, il ne reste plus qu’à ajouter ces deux valeurs dans une formule unique :
=MAX(SI(B9<$H$10;MIN(C9;$H$10)-B9;MIN($H$10+1;C9+SI(C9<B9;1;0))-MAX($H$9;B9));0)