Comment calculer les heures entre deux horaires qui s’étendent au-delà de minuit dans Excel ?
Supposons que vous gériez un planning de travail dans Excel et que vous deviez suivre des plages horaires débutant tard dans la journée et se terminant après minuit, le lendemain. Par exemple, la colonne A indique les heures de début le jour même, tandis que la colonne B consigne les heures de fin après minuit le jour suivant. Lorsque vous utilisez une formule simple de soustraction telle que « =B2-A2 », Excel renvoie un résultat incorrect ou négatif, comme le montre la capture d’écran ci-dessous. Ce phénomène s’explique par le fait qu’Excel réinitialise son calcul horaire à minuit, rendant ainsi la soustraction directe inexacte pour toute période traversant minuit. Comment calculer avec précision le nombre total d’heures travaillées entre deux horaires lorsque l’heure de fin survient après minuit dans Excel ?
Calculer les heures entre deux horaires après minuit à l’aide d’une formule
Calculer les heures entre deux horaires après minuit avec du code VBA (automatisation avancée)
Calculer les heures entre deux horaires après minuit à l’aide d’une formule
Pour obtenir un calcul précis des heures entre deux horaires dont l’heure de fin est postérieure à minuit, vous pouvez utiliser une formule simple qui tient compte des différences horaires s’étalant sur deux jours. Cette méthode est efficace pour des jeux de données de petite à moyenne taille ou lorsque votre logique se limite à gérer des écarts horaires traversant minuit.
Fonctionnement : La formule « =(B2-A2+(B2<A2))*24 » vérifie si l’heure de fin (B2) est antérieure à l’heure de début (A2). Si c’est le cas, elle ajoute 1 jour — puisqu’Excel stocke les heures sous forme de fractions d’une journée de 24 heures — garantissant ainsi un résultat toujours positif.
1. Saisissez la formule suivante dans une cellule vide située à côté de vos données horaires (par exemple, en cellule C2) :
=(B2-A2+(B2<A2))*24Explication : Ici, A2 contient l’heure de début et B2 contient l’heure de fin (après minuit). Ajustez les références de cellules en fonction de la disposition de vos données.
2. Après avoir saisi la formule, appuyez sur Entrée, puis utilisez la poignée de recopie pour l’étirer vers les autres lignes concernées. Excel calcule instantanément les différences horaires en tenant compte du passage à minuit.
Conseils :
- Assurez-vous que toutes les cellules horaires sont formatées en tant que Heure (clic droit > Définir le format des cellules > Heure).
- Cette formule affiche les heures sous forme décimale. Pour obtenir un résultat en heures et minutes, utilisez le formatage
TEXTE, par exemple :=TEXT(B2-A2+(B2<A2),"h:mm"). Toutefois, la formule d’origine reste préférable lorsque vous avez besoin de calculs horaires décimaux pour la paie ou les rapports. - Si vos données contiennent des entrées horaires non valides, vérifiez les éventuelles fautes de frappe ou les valeurs qu’Excel ne reconnaît pas comme des heures.
Erreurs courantes :
- Si vous obtenez une erreur #VALEUR!, assurez-vous que les deux colonnes contiennent bien des valeurs horaires valides et non du texte.
- Des résultats négatifs ou inattendus indiquent généralement que les références de la formule sont décalées ou qu’une heure n’a pas été saisie correctement.
Résumé : Cette solution est idéale pour les plannings de travail classiques ou le suivi simple des horaires nocturnes, dès lors que vos données s’inscrivent dans une configuration à deux colonnes directe et que vous n’avez pas besoin d’une logique métier complexe.
Calculer les heures entre deux horaires après minuit avec du code VBA (automatisation avancée)
Si vous devez traiter un grand volume de données horaires ou automatiser le calcul d’heures dans des scénarios complexes — comme appliquer un arrondi personnalisé, gérer des exclusions ou intégrer des pauses variables — une macro VBA peut vous faire gagner du temps tout en réduisant les erreurs manuelles. Cette approche s’adresse aux utilisateurs avancés ainsi qu’à ceux qui gèrent des plannings évolutifs, des points de coupure atypiques ou des besoins de traitement par lots.
Scénarios d’application :Choisissez cette solution si vous :
- Traiter régulièrement des centaines, voire des milliers de lignes
- Avoir besoin d’une logique plus complexe qu’un simple passage à minuit (par exemple, règles variables pour les heures supplémentaires, déductions de pauses déjeuner)
- Préférer une solution qui se recalcule automatiquement avec un minimum de saisie manuelle
Inconvénients : Les utilisateurs doivent être à l’aise avec l’activation des macros, et les destinataires du classeur devront également autoriser le VBA ou ajuster les paramètres de sécurité des macros.
1. Ouvrez l’éditeur Visual Basic pour Applications (VBA) en appuyant sur Alt + F11. Dans l’interface VBA, cliquez sur Insertion > Module pour créer un nouveau module, puis collez le code ci-dessous dans la fenêtre de code :
Sub CalculateHoursAcrossMidnight()
Dim rng As Range
Dim cell As Range
Dim startTime As Date
Dim endTime As Date
Dim resultCol As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range containing start and end times (two columns)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resultCol = rng.Columns(rng.Columns.Count).Column + 1
For Each cell In rng.Columns(1).Cells
startTime = cell.Value
endTime = cell.Offset(0, 1).Value
If IsDate(startTime) And IsDate(endTime) Then
If endTime < startTime Then
cell.Offset(0, 2).Value = (endTime - startTime + 1) * 24
Else
cell.Offset(0, 2).Value = (endTime - startTime) * 24
End If
Else
cell.Offset(0, 2).Value = "Invalid Time"
End If
Next cell
MsgBox "Calculation complete! Hours between times after midnight are now in the next column.", vbInformation, xTitleId
End Sub2. Pour exécuter la macro, fermez l’éditeur VBA et revenez à Excel. Sélectionnez dans votre feuille de calcul la plage contenant à la fois les colonnes d’heure de début et d’heure de fin, puis appuyez sur la touche F5 ou cliquez sur le bouton Exécuter.
La macro vous invitera à sélectionner une plage contenant les heures de début et de fin, placées côte à côte. Les résultats — exprimés en nombre d’heures entre ces horaires, avec ajustement automatique pour le passage à minuit — seront inscrits dans la colonne immédiatement à droite de votre plage de données.
Conseils et dépannage :
- Les colonnes d’heure de début et de fin doivent toutes deux contenir des valeurs de date/heure valides ; les cellules dont le format n’est pas reconnu seront marquées comme « Heure non valide ».
- Pour réutiliser ou adapter la macro à d’autres feuilles, modifiez simplement la sélection ou ajustez les décalages de colonnes selon vos besoins.
- Si la macro n’est pas visible ou exécutable, veillez à ce que les macros soient activées en ajustant les paramètres de sécurité d’Excel (Fichier > Options > Centre de gestion de la confidentialité > Paramètres des macros).
- Cette approche peut être adaptée pour gérer des règles métier personnalisées : il vous suffit d’ajouter une logique VBA supplémentaire afin de prendre en compte les pauses, l’arrondi horaire ou le calcul des heures supplémentaires, selon vos besoins.
Résumé : La méthode automatisée par VBA est particulièrement utile pour les utilisateurs qui gèrent de grands effectifs, ont besoin de calculs récurrents ou appliquent des règles organisationnelles spécifiques à la gestion du temps. Une fois configurée, elle élimine les tâches manuelles répétitives et réduit au minimum les risques d’erreurs de calcul pour les services de nuit.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels