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

Dans ce tutoriel, je vais vous montrer comment gérer des plages horaires complexes, comme celles qui son à cheval sur deux journées dans Excel. Nous verrons également comment développer vos propres fonctions Excel pour rendre ces tâches encore plus simples et automatisées.

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, prenons l’exemple d’un service de livraison qui fonctionne jour et nuit.

Voici les horaires réalisés par un de ses livreurs sur une semaine :

Ici, nous allons souhaiter calculer la durée totale de chaque journée, et identifier les heures réalisées uniquement pendant la nuit (entre 22h et 6h).

2. Calculer la durée totale d’une plage horaire

Pour calculer la durée totale d’une plage horaire à cheval sur deux journées, 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.

En effet, dans ce cas de figure précis, l’heure de fin serait inférieure à l’heure de début, et donc le résultat retourné serait négatif.

Une soustraction classique fonctionne correctement tant que les horaires restent dans la même journée.

Par exemple, si l’heure de début est 08:00 et l’heure de fin est 16:00, le calcul 16:00 - 08:00 donnera 8:00, ce qui est correct.

Cependant, dès que l’horaire traverse minuit, le résultat obtenu devient incorrect et plutôt que de présenter un résultat incohérent, Excel préfère retourner une erreur.

Ainsi, si nous saisissons la formule suivante dans la cellule D9 nous obtiendrons une erreur Excel. 

En effet, ici l’heure de début est 20:00 et l’heure de fin est 03:00 le lendemain.

Le résultat dans la cellule D9 sera affiché sous forme de dièses (#####).

En passant le curseur de la souris sur la cellule, Excel affiche un message d’information indiquant :

« Les dates et heures négatives ou trop longues s’affichent sous la forme #####. »

Pour résoudre ce problème, nous utilisons la fonction MOD, qui permet de calculer une durée tout en tenant compte des horaires qui traversent minuit.

La fonction MOD (ou RESTE en français) est idéale pour traiter les horaires traversant minuit. Lorsqu’on soustrait une heure de fin à une heure de début, si cette plage couvre minuit, le résultat peut être négatif. La fonction MOD permet de contourner ce problème en ajustant la durée dans une plage de 24 heures.

La syntaxe de la fonction est :

 =MOD(Nombre;Diviseur) 

• Nombre : c’est la valeur à diviser. Ici, il s’agit de « Heure de fin » - « Heure de début ».
• Diviseur : pour les horaires, il vaut toujours 1, car une journée correspond à 1 dans le format horaire Excel.

Saisissons maintenant la formule suivante dans la cellule D9 :

 =MOD(C9-B9;1) 

• C9 correspond à l’heure de fin.
• B9 correspond à l’heure de début.
• Et 1 correspond à une journée complète dans le système de gestion des dates et heures d’Excel, et il est utilisé comme base pour ramener la différence entre C9 et B9 dans une plage positive comprise entre 0 et 1 (nous y reviendrons juste après).

Il faut en effet savoir qu’Excel considère les horaires comme des fractions de journée. Voici quelques exemples :

• 00:00 (minuit) correspond à 0, car il s’agit du début de la journée.
• 12:00 correspond à 0,5 (12 heures divisées par 24).
• 24:00 (minuit le jour suivant) correspond à 1, soit une journée complète.

Cela signifie que toutes les heures sont représentées par des valeurs comprises entre 0 et 1.

Ainsi, lorsque nous soustrayons une heure de début supérieure à l’heure de fin (par exemple, 20:00 - 03:00), Excel retourne une valeur négative, car 0,125 (03:00) est inférieur à 0,833 (20:00). C’est donc là qu’intervient la fonction MOD. Elle corrige automatiquement cette différence en ajoutant 1 (une journée complète) lorsque la valeur est négative. Cela ramène toujours la durée dans une plage positive.

Cela revient à saisir la formule suivante :

 =C9-B9+1 

Sauf qu’avec MOD, le « +1 » n’est ajouté que lorsque la valeur est négative.

Enfin, cette valeur est convertie au format horaire (HH:MM). Comme 0,2917 représente environ 7 heures (0,2917 × 24 = 7), le résultat final est 07:00, ce qui correspond bien à la durée entre 20:00 et 03:00.

Il ne reste plus qu’à étendre la formule vers le bas pour l’appliquer à toutes les lignes.

La fonction retournera la durée totale pour chaque plage horaire, même si celle-ci traverse minuit.

Astuce : Assurez-vous que toutes les colonnes d’heure sont correctement formatées en « Heure » via un clic droit > Format de cellule > Heure.

3. La fonction CALCULDUREE()

Pour aller plus vite en évitant de devoir ressaisir cette formule à chaque fois que nous devons calculer des durées, nous pouvons également créer notre propre fonction Excel.

Cette fonction personnalisée calculera automatiquement la durée entre deux horaires, y compris lorsqu'ils traversent minuit.

Ce type de fonction est appelé une fonction personnalisée, et elle doit être développée dans le langage VBA (Visual Basic for Applications).

Ce langage de programmation intégré dans Excel (et dans les autres applications de la suite Office, comme Word et Access) permet d’automatiser des tâches répétitives, de créer des macros, et d’ajouter des fonctionnalités avancées, comme des fonctions personnalisées utilisables directement dans les feuilles de calcul.

En d’autres termes, grâce au VBA, nous pouvons étendre les fonctionnalités d’Excel et adapter ses outils à nos besoins spécifiques. Ici, nous allons créer une fonction personnalisée pour simplifier le calcul des durées.

Si vous débutez en VBA et souhaitez en apprendre davantage, je vous invite à découvrir mon livre « Le VBA pour les grands débutants », disponible en cliquant ici.

Pour écrire notre fonction, nous devons d’abord accéder à l’éditeur VBA, qui est une interface dédiée au développement. Elle nous permet d’écrire, de modifier et de gérer le code VBA associé à nos classeurs Excel.

Il existe plusieurs méthodes pour lancer cet éditeur, la plus rapide étant d’utiliser le raccourci clavier [Alt]+[F11].

Une fois dans l’éditeur VBA, nous devons ajouter un module pour y écrire notre code, en cliquant sur le menu Insertion > Module.

Un nouveau module vide s’ajoute dans le projet. C’est ici que nous allons écrire notre fonction.

Maintenant, pour créer une fonction utilisable dans les cellules d’une feuille de calcul, nous devons commencer par utiliser l’instruction Function, suivi du nom de la fonction

Function calculeDuree

Ensuite, si nous le souhaitons, nous pouvons venir saisir les arguments de la fonction entre parenthèses.

Cette fonction prendra deux arguments :

• hDebut : l’heure de début,
• hFin : l’heure de fin.

Ces arguments doivent être typés pour indiquer à Excel qu’ils représentent des nombres (plus précisément des fractions de journée, comme Excel stocke les heures). Nous les déclarons en tant que Double, qui est un type numérique précis.

Function calculeDuree(hDebut As Double, hFin As  Double)
End Function

À l’intérieur de la fonction, nous ajoutons la logique pour calculer la durée laquelle consiste à calculer la différence entre hFin et hDebut (nous affectons cette valeur directement à la fonction) :

calculeDuree = hFin - hDebut

Enfin, si l’heure de fin est inférieure à l’heure de début, cela signifie que la plage horaire traverse minuit.

Dans ce cas, et comme nous l’avons vu juste avant, nous devons ajouter 1 (une journée complète) à la différence.

If calculeDuree < 0 Then calculeDuree  = calculeDuree + 1

Une fois la fonction terminée, nous revenons dans la feuille de calcul.

Nous pouvons maintenant tester la fonction directement dans une cellule, comme nous le ferions pour n’importe quelle autre fonction d’Excel.

Par exemple, dans la cellule D9, nous saisissons :

 =calculeDuree(B9;C9) 

Où B9 correspond à l’heure de début (par exemple, 20:00), et C9 correspond à l’heure de fin (par exemple, 03:00).

Le résultat affiché sera 07:00, qui correspond bien à la durée totale entre 20:00 et 03:00.

L’utilisation d’une fonction personnalisée présente plusieurs avantages :

• Gain de temps : une fois créée, vous pouvez réutiliser cette fonction dans toutes vos feuilles de calcul sans ressaisir de formules complexes.
• Lisibilité : la fonction calculeDuree rend vos calculs plus clairs et compréhensibles, surtout si d’autres utilisateurs doivent consulter vos fichiers.
• Polyvalence : vous pouvez adapter la fonction à d’autres besoins, comme le calcul des heures de nuit ou l’ajout de plages horaires spécifiques.

Par contre, pour conserver votre fonction personnalisée, enregistrez le fichier au format Classeur Excel avec macros activées (.xlsm). Si vous utilisez un format classique (.xlsx), le code VBA sera supprimé.