Comment calculer les durées de travail complexes (à cheval sur deux jours) dans Excel ?

comment calculer des horaires complexes avec des heures de nuit dans Excel bonjour à tous c'est Alex excelformation.fr et dans ce tutoriel je vais vous montrer une technique qui va permettre de calculer automatiquement des heures de travail comprenant des heures de nuit dans Excel ce qui n'est pas possible de base car ici Excel nous retourne une erreur nous verrons donc une astuce qui va consister à utiliser une fonction très puissante et très simple à mettre en place qui va permettre de calculer les heures de travail même lorsque le poste est à cheval sur deux journées alors trouvverz 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] prodexcel 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 corrigé en suivant le lien disponible dans la description de la vidéo ce qui vous permettra de iser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo alors comme je viens de vous l'expliquer dans l'introduction nous allons maintenant voir comment calculer des horaires complexes avec des heures de nuit en utilisant Excel et pour illustrer ce tutoriel nous allons pouvoir prendre l'exemple d'un service de livraison qui fonctionne jour et nuit nous avons actuellement à l'écran les horaires réalisés par un de ces livreurs sur une semaine et ici nous allons souhaiter calculer la durée totale d'une plage horaire à cheval sur 2 jours nous ne pouvons pas nous contenter de simplement soustraire l'heure de fin à l'heure de début comme nous le ferions pour calculer une durée classique pour nous en rendre compte nous commençons par sélectionner les cellules C9 jusqu'à C 13 dans lesquels nous allons vouloir mettre en place le calcul puis nous partons de l'heure de fin ici 3h du matin à laquelle nous allons souousstraire à l'URE de début si 20h ensuite nous allons valider en appuyant sur la touche Contrôle et entrer ce qui va permettre de valider la formule sur toutes les cellules préalablement sélectionnées à ce moment-là nous allons pouvoir constater que effectivement et Excel nous calcule les heures de travail lorsque l'heure de fin est supérieure à l'heure de début donc il s'agit ici d'une journée classique en revanche dans le cas contraire nous aurons une erreur car si nous laissons le curseur de la souris au-dessus de l'une de ces cellules nous voyons le message les datur négative ou trop longue s'affiche sous la forme de di en effet ici 3h - 20h va nous donner - 17h ce qui n'est pas gérable directement par Excel et donc pour résoudre ce problème nous allons utiliser la fonction mode qui permet de calculer une durée tout en tenant compte des horaires qui traversent minuit en effet cette fonction mode donc mod pour modulo dont l'équivalent français serait rest est idéal pour traiter les horaires traversant minuit lorsque l'on soustrait une heure de fin à une heure de début si cette plage couvre minuit le résultat sera négatif ce que nous avons vu tout à l'heure et donc à ce moment-là la fonction mode va contourner ce problème en ajustant la durée dans une plage de 24 he donc comme vous pouvez le voir actuellement à l'écran cette fonction mode attend en deux arguments le premier l'argument nombre et la valeur que nous allons vouloir diviser ici il s'agit donc du calcul de l'heure de fin moins l'heure de début le deième argument diviseur sera toujours égal à 1 pour les horaires car une journée correspond dans Excel à 1 et pour obtenir des heures ce sera uniquement une portion de cette valeur donc ici nous allons nous placer après notre C9 - B9 mettre un point virgule et donc définir que le diviseur sera égal à 1 encore une fois nous validons en appuyant sur contrôle et entrée et maintenant nous avons de manière très simple le résultat qui correspond aux heures travaillées par l'employé ici celui-ci a commencé à 20h pour aller jusqu'à minuit il a donc travailler 4h auquelles s'ajoutent les 3h du lendemain matin ce qui fait donc effectivement 7h comme vous pouvez le voir ici la fonction mode a corriger l'horaire en ajoutant 1 c'est-à-dire une journée complète lorsque la valeur est négative afin de ramener la durée dans une plage positive comprise entre 0 et 1 alors si nous souhaitons aller un petit peu plus vite sans devoir ressaisir cette formule à chaque fois fois que nous voulons calculer des durées nous pouvons également créer notre propre fonction Excel il s'agira alors d'une fonction personnalisée qui va calculer automatiquement la durée entre deux horaires y compris lorsque celui-ci traverse minuit qu'il est donc à cheval sur de jours ce type de fonction doit être développé dans le langage VBA qui est le langage de programmation intégré dans Excel ainsi que dans les autres applications de la suite Office comme Word et Access qui permet d'automatiser des tâches répétitives de créer des macros mais également d'ajouter des fonctions personn personnalisé comme celle que nous allons mettre en place dans ce tutoriel et donc en d'autres termees c'est grâce au VBA que nous pouvons étendre les fonctionnalités d'Excel et adapter ces outils à nos besoins spécifiques et si jamais vous débutez dans le VBA et que vous souhaitez en savoir plus je vous invite à découvrir mon livre le VBA pour les grands débutants en suivant le lien disponible dans la description de la vidéo étant donné que dans le cadre de ce tutoriel nous allons passer assez vite sur ces notions de VBA voilà alors il faut savoir que la première chose à faire pour pouvoir créer une fonction VBA et d'accéder à l'éditeur VBA qui est une interface dédiée au développement celle-ci va nous permettre d'écrire de modifier et de gérer le code VBA associé à un classeur Excel et il existe plusieurs méthodes pour le lancer la plus rapide consistant à utiliser le raccourci clavier Alt F11 alors attention si vous êtes sur un clavier réduit de type PC portable alt F11 n'aura pas forcément pour effet de lancer l'éditeur de code VBA étant donné qu'il faudra en même temps appuyer sur la touche fonction situé la plupart du temps en bas à gauche pour que la touche F11 puisse produire son effet donc ici nous avons bien lancé l'éditeur de code VBA et nous sommes directement sur le projet c'est-à-dire sur le classeur Excel sur lequel nous sommes en train de travailler nous allons maintenant souhaiter ajouter un nouveau module dans le projet qui est une feuille dans laquelle nous allons pouvoir écrire notre code pour insérer ce nouveau module nous nous rendons dans le menu Insertion et nous cliquons tout simplement sur module maintenant pour créer une fonction utilisable directement dans les cellules de la feuille de calcul nous allons commencer par utiliser l'instruction function suivie du nom que nous souhaitons donner à la fonction ici nous allons l'appeler calcul durée cela étant fait nous pouvons appuyer sur la touche entrere pour valider la création de la fonction et comme vous pouvez le constater automatiquement l'éditeur de code VBA a ajouté deux fonctionnalités sur notre fonction il a tout d'abord insérer des parenthèses ouvrantes et fermantes dans lesquelles nous allons revenir juste après afin de pouvoir insérer des arguments à notre fonction pour la personnaliser il a également ajouté la ligne function qui vient marquer la fin de notre fonction et donc tout ce que nous allons ici entre ces deux lignes sera exécuté à chaque fois que nous appellerons la fonction calcul durée voilà donc maintenant nous nous plaçons à l'intérieur des parenthèses et nous allons vouloir spécifier deux arguments le premier nous allons l'appeler h début c'est-à-dire heure de début et le 2è nous allons l'appeler h fin comme vous pouvez l'imaginer ensuite nous allons donner un type à chacun de ces arguments c'est-à-dire que nous allons spécifier quel type d'information nous allons vouloir manipuler ici pour manipuler des heures nous allons les typer en tant que double c'est-à-dire en tant que nombre avec des virgules nous allons faire de même pour HF donc as double et comme vous pouvez le constater pour affecter un type particulier à une variable ou ici à un argument nous utilisons tout simplement le motclé as ensuite nousous plaçons à l'intérieur de cette fonction et nous allons y ajouter la logique pour calculer la durée laquelle consiste à calculer la différence entre h fin et h début en affectant directement cette valeur à la fonction donc pour cela nous appelons le nom que nous nous avons donné à la fonction c'est-à-dire calculer durée nous utilisons l'opérateur égal pour lui affecter une valeur cette valeur qui sera égale à H fin Mo h début alors maintenant rappelez-vous dans la première partie de ce tutoriel nous avons vu qu'il n'était pas possible de gérer des heure négative nous allons donc voir pour adapter ce résultat dans le cas où effectivement ce résultat serait inférieur à ZrO donc nous allons utiliser l'instruction ifzen de VBA qui permet d'effectuer un test afin de retourner un résultat en fonction du résultat de ce test ici nous voulons vérifier si calcul durée est inférieur à é0 lorsque ce sera le cas nous ajouterons tout simplement 1 à la valeur de calcul durée calcul durée est égal à son propre résultat auquel nous ajoutons 1 et voilà comme vous pouvez le constater cette fonction est très simple elle n'est constituée que de deux lignes nous allons maintenant pouvoir revenir dans notre feuille de calcul supprimer les formules que nous avons mis en place tout à l'heure et ensuite pour appeler la fonction calcul durée nous allons faire comme pour n'importe quelle autre fonction Excel c'est-à-dire que nous tapons le symbole égal pour informmer Excel que nous allons venir saisir une formule nous saisissons le nom que nous avons défini c'est-à-dire calcule durée puis nous allons définir le premier argument donc qui correspond à hdbut c'est-à-dire l'heure de début qui est égale à la valeur de la cellule B9 pour H fin nous allons récupérer la valeur de C9 ensuite nous fermons la parenthèse et comme nous l'avons fait précédemment dans cette vidéo nous allons valider en appuyant sur contrôle entrée et à ce moment-là vous pouvez constater que le résultat retourner est exactement le même sauf que ici utiliser la fonction calcul durée est beaucoup plus simple et beaucoup plus logique d'utiliser la fonction modulo avec le diviseur 1 comme vous pouvez le constater l'utilisation d'une fonction personnalisée est très puissant et va nous permettre tout d'abord de gagner du temps car une fois que la fonction est créée nous pouvons la réutiliser dans notre feuille de calcul autant que nous le souhaitons si nous voulons nous pouvons également l'enregistrer dans notre classeur de macro personnel afin de pouvoir l'utiliser cette fois-ci dans T les feilles de calcul que nous allons lancer sur le poste de travail utiliser une fonction personnalisée est également beaucoup plus lisible étant donné que nous voyons directement ce que nous allons faire en appelant celle-ci pour peu que nous ayons utilisé un nom qui soit suffisamment explicite alors si vous voulez aller encore un petit peu plus loin rendez-vous la semaine prochaine car cette fois-ci nous verrons comment calculer uniquement les heures de nuit c'est-à-dire les heures qui appartiennent à un intervalle donné également à cheval sur de journées 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é vidé 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 des 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