Comment calculer les Heures de Nuit dans Excel (découvrez la formule incroyable) ?

comment calculer des heures correspondants à une plage de nuit sur Excel bonjour à tous c'est Alex excelformation.fr et dans ce tutoriel je vais vous montrer comment calculer précisément les heures de nuit travaillées avec Excel en fonction donc d'une plage horaire de nuit qui correspondra aux usages de l'entreprise bien entendu vous n'avez pas besoin d'être un expert en Excel pour suivre ce tutoriel étant donné que je vais vous expliquer chacune des manipulations et des formules de manière très précise alors si vous êtes livreur infirmier ou gestionnaire en Ressources Humaines vous pourrez constater que ce tutoriel va vous simplifier la vie en effectuant ce calcul avec une simple formule alors vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un [Musique] proexcel comme d'habitude si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans le cadre de cette vidéo vous pouvez télécharger le support de ce tutoriel ainsi que son corriger en suivant le lien disponible dans la description de la vidéo ce qui vous permettra de réaliser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo alors pour illustrer ce tutoriel nous allons pouvoir reprendre le tableau que nous avions utilisé dans la vidéo précédente dans laquelle nous avions calculé des heures de travail à cheval sur de journées ici nous avons donc ajouté la colonne qui permet de calculer uniquement les heures qui correspondent aux heures de nuit et également donc ici un autre tableau avec l'heure de début et l'heure de fin qui correspondent aux horaires de nuit de l'entreprise et donc pour rappel nous retrouvons dans ce tableau une première colonne avec les dates puis une colonne avec les heures de début une colonne avec les heures de fin et dans la 4ème colonne de ce tableau nous avions calculé la durée de travail totale en tenant compte que ici la journée de travail est à cheval sur 2 jours donc vous l'aurez bien compris l'objectif de ce tutoriel sera maintenant de déterminer combien d'heures dans la plage de travail tombe pendant la période nocturne c'est-à-dire qui correspond aux heures que nous avons ici alors il faut savoir que le calcul des heures de nuit est un petit peu complexe car il doit tenir compte des spécificités des plages horaires notamment lorsque celle-ci chevauche min nuuit c'est pourquoi ilelle est indispensable de décomposer la formule en deux parties afin d'obtenir des résultats fiables dans toutes les situations donc ici nous avons deux colonnes spécifiques qui vont permettre de calculer dans un premier temps les heures lorsque le début de la prise de poste est antérieur à l'horaire de fin de nuit et ensuite nous calculerons l'inverse c'est-à-dire lorsque le début de la prise de poste est antérieure à l'heure de fin de nuit ensuite il nous suffira d'additionner ces deux valeurs pour obtenir le total de la nuit voilà donc si nous regardons la première ligne nous voyons que effectivement l'heure de fin de poste est inférieure à l'heure de début cela veut dire que le poste est bien à cheval sur de journées et qu'il chevoche minuit nous allons alors utiliser la formule suivante que nous allons saisir ici dans la cellule E9 et qui va consister à prendre la valeur la plus petite entre l'URE de fin de travail et l'heure de fin des heures de nuit donc pour obtenir la valeur la plus petite nous utilisons la fonction min dans laquelle nous allons venir saisir des séries de valeurs au sein de laquelle se trouve la valeur la plus petite que nous allons vouloir extraire donc ici nous allons comparer la valeur de la cellule C9 c'est-à-dire ici l'heure de fin de poste avec la valeur de la cellule J10 dans laquelle se trouve l'horaire de fin de nuit ici nous allons appuyer sur la touche F4 afin d'insérer automatiquement les symboles dollar sur les coordonnées donc de la cellule J10 ce qui permettra ensuite d'étendre la formule sur toutes les cellules de la colonne en étant sûr que cette cellule J10 soit toujours celle qui soit utilisée dans la formule ensuite nous fermons la parenthèse et nous pouvons valider et nous voyons que ici il y a effectivement 3 heures qui s'écoule ensuite nous allons pouvoir soustraire à ce résultat l'heure de début de travail qui se trouve ici dans la cellule B9 afin de calculer la durée réelle passé dans les heures de nuit donc B9 et donc ici nous pouvons constater que nous avons une erreur qui est lié au fait que l'heure de début de prise de poste est bien supérieure à l'heure de fin nuit cela a pour effet de générer une valeur négative qui correspond ici aux 3h que nous avions calculé juste avant 20h c'est-à-dire - 17 or Excel n'est pas capable de gérer des heures négatives ce que nous pouvons constater en passant le curseur de la souris au-dessus de la cellule et donc pour éviter ce phénomène nous allons pouvoir utiliser la fonction Max qui va permettre de comparer ce résultat avec zéro et de nous retourner la valeur la plus élevé entre les deux de cette manière nous sommes sûrs que nous aurons soit des valeurs positives soit la valeur zéro mais en aucun cas nous n'aurons d'horaire négatif voilà donc nous en avons fini pour la première partie de cette formule qui comme vous pouvez le constater est très simple à mettre en place ici nous voyons que les cas dans lesquel nous aurons effectivement un horaire qui neous sera retourné c'est sur la deuxième ligne dans laquelle la personne prend le poste à 2h et termine à 9h entre 2 et 6h il y a effectivement 4h de la même manière lorsque la personne commence à 4 4 He elle va travailler 2h avant d'arriver à 6h voilà donc maintenant nous en arrivons à la deuxième partie de cette formule qui est un petit peu plus complexe à mettre en place et qui va consister à prendre en compte le cas où l'heure de début de travail est postérieure à l'heure de fin des heures de nuit cela signifiera donc que la placeage horaire chevau che minuit et que l'heure de fin des heures de nuit se situe le jour suivant et donc dans ce cas-là l'astuce va consister à ajouter un au résultat de l'heure de fin c'est-à-dire que nous allons considérer que les 3 heures correspondent aux 3h du lendemain qui correspond effectivement à la réalité et donc pour commencer nous allons vouloir calculer la valeur minimale entre l'heure de fin de travail que nous avons ici dans la cellule J10 que nous passons en valeur absolue en insérant des symboles dollars et que nous allons passer au lendemain en ajoutant un valeur que nous allons vouloir comparer avec l'heure de fin de travail que nous allons obtenir en récupérant la valeur de la cellule C9 à laquelle nous allons vouloir ajouter également une journée uniquement si celle-ci est inférieure à l'heure de début ce qui signifiera que effectivement nous aurons traversé minuit donc pour ajouter uniquement lorsque c'est nécessaire une journée nous utilisons la fonction SI qui permet d'effectuer un test afin de retourner une valeur différente en fonction du résultat de ce test si ce test est égal à vrai c'est-à-dire que l'heure de fin est effectivement inférieure à l'heure de début alors nous ajouterons une journée complète dans le cas contraire nous n'ajouterons rien du tout ensuite nous fermons la parenthèse de cette fonctionci puis la parenthèse de la fonction min et maintenant nous avons déterminé l'heure de fin de travail nous allons pouvoir retirer l'heure de début qui va cette fois-ci correspondre à la valeur maximale entre l'heure de début de nuit donc ici la cellule J9 que nous allons passer en valeur absolue et la valeur de la cellule B9 dans laquelle se trouve le début de la prise de poste ensuite nous fermons la parenthèse et comme d'habitude nous validons en appuyant sur contrôle entrée lorsque effectivement le début de la prise de poste se situe après la fin de la nuit nous aurons un résultat et dans le cas contraire nous nous aurons une erreur qui pour rappel correspond à un horaire négatif pour empêcher donc cette valeur nous allons comme tout à l'heure utiliser la fonction max pour comparer ce résultat avec ZrO de cette manière toutes les valeurs négatives seront remplacées par des zéos et donc pour calculer leur total réalisé au cours des horaires de nuit il va maintenant suffire d'additionner les deux valeurs que nous venons de calculer dans une seule et même cellule pour nous rendre compte donc ici la personne qui a travaillé de 20h jusqu'à 3h du matin a effectué 5h d'horaire de nuit celle qui a travaillé de 2 à 9 en a effectué 4 ici une personne qui aurait commencé de 6h à 14h à travaillé uniquement la journée le don nuit est donc égal à zé nous pouvons même constater que sur la dernière ligne la personne a effectué deux plages de nuit différentes une fois le matin parce qu'elle a commencé à 4h du matin ici elle a réalisé 2h mais elle a également réalisé une demi-heure le soir en finissant son poste à 22h30 alors bien entendu une telle plage horaire n'est pas possible mais c'est uniquement pour montrer que la formule fonctionne de manière correcte que les heures de nuit a étaient réalisé le matin ou le soir pour finir si nous souhaitons utiliser une seule cellule pour effectuer le calcul total il va tout simplement suffire de venir dans la première colonne afin de copier la formule en la sélectionnant et en appuyant sur contrôle C puis ici nous allons venir remplacer E9 par la formule qui est insérée dans la cellule et nous allons faire de même donc ici pour la formule de la cellule F9 nous la sélectionnons nous appuyons sur contrôle C nous revenons dans la dernière cellule et cette fois-ci nous allons supprimer F9 et insérer en appuyant sur contrôle V la formule que nous avons récupéré juste avant ensuite donc il nous suffira d'adapter la formule sur toutes les cellules de la colonne et maintenant si nous le souhaitons nous pouvons très largement supprimer les deux colonnes qui n'étaient là que pour nous permettre de comprendre comment réaliser cette formule donc comme d'habitude vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo si la vidéo vous a plu n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux et vous pouvez également me laisser vos impressions dans l'espace de les commentaires je lirai chacun d'entre eux et y répondrai avec plaisir évidemment si ce n'est pas encore fait abonnez-vous à la chaîne pour ne manquer aucune astuce c'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto